0

Please login or register to vote for this query.

(click on this box to dismiss)

Duplicate of http://sqlfiddle.com/#!3/24476/4 just for data.se advertising :-)

Q&A about the site for people organizing, marketing or licensing open source development projects

CREATE TABLE #tbl_Bookings (
  Id int, DateStarted DateTime, DateEnded DateTime, RoomID int)

INSERT INTO #tbl_Bookings (Id, DateStarted, DateEnded, RoomID)
SELECT 1, '05/07/2012 09:00', '05/07/2012 10:00', 1 UNION ALL
SELECT 2, '05/07/2012 12:00', '05/07/2012 13:00', 1 UNION ALL
SELECT 3, '05/07/2012 08:45', '05/07/2012 11:00', 2 UNION ALL
SELECT 4, '05/07/2012 08:00', '05/07/2012 09:00', 3 UNION ALL
SELECT 5, '05/07/2012 09:30', '05/07/2012 10:00', 3

DECLARE @Start DateTime = '05/07/2012 08:30'
DECLARE @End DateTime = '05/07/2012 13:30'

;WITH Bookings (RoomId, RowNum, Started, Ended) AS (
  SELECT RoomId,
      ROW_NUMBER() OVER (PARTITION BY RoomId ORDER BY DateStarted) AS RowNum,
      DateStarted, DateEnded
  FROM #tbl_Bookings)
SELECT RoomId, B.Ended AS S, C.Started AS E
FROM Bookings B
  CROSS APPLY (
    SELECT B2.Started FROM Bookings B2
    WHERE B2.RowNum = B.RowNum + 1
      AND B2.Started <= @End
      AND B2.RoomId = B.RoomId
  ) C
WHERE B.Ended >= @Start
  UNION
SELECT RoomId, @Start, MIN(DateStarted)
FROM #tbl_Bookings
WHERE DateStarted > @Start
  AND NOT EXISTS (
    SELECT 1 FROM Bookings WHERE Started < @Start AND Ended > @Start
  )
GROUP BY RoomId
  UNION
SELECT RoomId, MAX(DateEnded), @End
FROM #tbl_Bookings
WHERE DateEnded < @End
  AND NOT EXISTS (
    SELECT 1 FROM Bookings WHERE Started < @End AND Ended > @End
  )
GROUP BY RoomId

fork query

created jul 18 12 Mark Hurd