How to filter results off complex sql statement

[KirkKelly]

I have the following query that returns records where there are gaps of times between meetings, and only ones that meet a certain amount of time gap, and are associated with a valid roomid. The problem I have is that it returns records that are past the last possible time to schedule in a room. The room table has two fields called schedfrom and sched to that are int. They store values such as 600 (for 6am) and 1700 (for 5pm). As you can see, part of the query check if a result is null and if it is, inserts a value from one of the parameters. I want to filter this result set to display only records that have a OpenSlotBegin value that is between the schedfrom and schedto for based off the room id. Below is my current code:


DECLARE @tiProcID int
DECLARE @Gap int
DECLARE @DateTimeBegin datetime
DECLARE @DateTimeEnd datetime
declare @tiLoops int
declare @tiLoopCtr int

SET @tiProcID = 7141
SET @gap = 90
SET @DateTimeBegin = '07/01/2002 07:00'
SET @DateTimeEnd = '07/05/2002 17:00'
set @tiLoopCtr = 0
set @tiLoops = datediff(dd,@DateTimeBegin,@DateTimeEnd)


SELECT roomname,m1.roomid,schedfrom,schedto,DATEADD(minute, 1, m1.endTime) AS OpenTimeBegin,
DATEADD(minute, @gap, m1.endTime) AS OpenTimeEnd
FROM meetings m1
LEFT JOIN meetings m2 ON ISNULL(m2.beginTime, @DateTimeEnd) > m1.endTime
AND (m1.roomID = m2.roomID OR m2.roomID IS NULL)
LEFT JOIN coRooms r1 ON r1.roomID = m1.roomID

WHERE DATEDIFF(minute, m1.endTime, ISNULL(m2.beginTime, @DateTimeEnd)) >= @gap+1
and (m1.begintime between @dateTimeBegin and @dateTimeEnd )
AND (m1.iscancelled=0) and m1.blockmember=0
AND ISNULL(m2.beginTime, @DateTimeEnd) =
( SELECT MIN(m3.beginTime)
FROM meetings m3
WHERE m3.beginTime >= m1.endTime AND (m3.roomID = m2.roomID OR m2.roomID IS NULL))
AND exists ( SELECT corooms.roomid
FROM dbo.coproc INNER JOIN
dbo.coProcRoomGrp ON dbo.coproc.procid = dbo.coProcRoomGrp.procID INNER JOIN
dbo.coRoomToGroup ON dbo.coProcRoomGrp.coRoomGrpID = dbo.coRoomToGroup.coRoomGrpID INNER JOIN
dbo.corooms ON dbo.coRoomToGroup.RoomID = dbo.corooms.roomid
WHERE dbo.coproc.procid = @tiProcID
and coRooms.RoomID = m1.roomID)

order by OpenTimeBegin,1


Thanks for any help