June 28, 2002 at 1:50 pm
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
Kirk Kelly
Kirk Kelly
July 2, 2002 at 12:12 pm
The simple approach would be to insert your SELECT statement into a temp table.
Then have a final SELECT from this temp table with the filter you desire.
Give this a try before complicating things further in your SELECT statement.
Another appoach is to break your routine into 2 or more steps, each step using a intermediate temp table. One of these temp tables can be prefiltered to the range of times before being joined to the other tables. In most cases your routine will also execute faster this way.
Take care.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply