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 )>
>>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 >> >>>>
>>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 >> >>>>