Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Complex Query Help
Message
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00611652
Message ID:
00611826
Views:
26
This select will return all avaialable slots. You'll have to add filtering on @DateTimeBegin and @DateTimeEnd
DECLARE @Gap int, @DateTimeBegin datetime, @DateTimeEnd datetime

SET @gap = 60
SET @DateTimeBegin = '01/25/2002 07:00'
SET @DateTimeEnd = '01/25/2002 17:00'

SELECT 
		DATEADD(minute, 1, m1.endTime) AS SlotBeginTime,
		DATEADD(minute, @gap, m1.endTime) AS SlotEndTime
	FROM meetings m1 
	LEFT JOIN meetings m2 ON ISNULL(m2.beginTime, @DateTimeEnd) > m1.endTime 
		AND (m1.roomID = m2.roomID OR m2.roomID IS NULL)
	WHERE DATEDIFF(minute, m1.endTime, ISNULL(m2.beginTime, @DateTimeEnd)) >= @gap+1  
		 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))
>Try this. You'll still miss a slot after the last record.
>
SELECT
>		DATEADD(minute, 1, m1.endTime) AS SlotBeginTime,
>		DATEADD(minute, @gap, m1.endTime) AS SlotEndTime
>	FROM meetings m1
>	JOIN meetings m2 ON m2.beginTime > m1.endTime AND m1.roomID = m2.roomID
>	WHERE DATEDIFF(minute, m1.endTime, m2.beginTime) >= @gap+1
>		 AND m2.BeginTime = ( SELECT MIN(m3.BeginTime) FROM meetings m3
>			WHERE m3.beginTime >= m1.endTime AND m3.roomID = m2.roomID )
>
>
>>Michael
>>
>>That one returned me two identical records that overlapped an existing case.
>>
>>Current Data:
>>
>>RoomID      BeginTime                EndTime
>>14          01/25/2002 07:30         01/25/2002 08:25
>>14          01/25/2002 08:33         01/25/2002 10:28
>>
>>
>>
>>The result set returned:
>>
>>RoomID       BeginTime              EndTime
>>14          01/25/2002 08:26        01/25/2002 09:26
>>14          01/25/2002 08:26        01/25/2002 09:26
>>
>>
>>
>>Kirk
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform