List available times from 2 DateTimes

Question

I know this one may come across a little confusing, I'm just trying to contemplating the best way of putting it across! I've posted this on a couple of forums now but I don't seem to be having any luck. Hopefully someone can offer some suggestions on how to do this.

Example table (tbl_Bookings)

ID  DateStarted       DateEnded         RoomID
1   16/07/2012 09:00  16/07/2012 10:00    1
2   16/07/2012 12:00  16/07/2012 13:00    1

Basically, I want to enter 2 date times, such as 16/07/2012 08:30 and 16/07/2012 13:30, and it will query my example table above and return 'available' times, IE, I'd like it to output the following.

16/07/2012 08:30 - 16/07/2012 09:00
16/07/2012 10:00 - 16/07/2012 12:00
16/07/2012 13:00 - 16/07/2012 13:30

My question is, is this entirely possible in SQL? I've tried to think of how to do it in VB and I'm struggling with that one too. My idea/attempt has been using Ron Savage's fn_daterange (as shown below)

if exists (select * from dbo.sysobjects where name = 'fn_daterange') drop function fn_daterange;
go
create function fn_daterange
(
@MinDate as datetime,
@MaxDate as datetime,
@intval  as datetime
)
returns table
as
return
WITH times (startdate, enddate, intervl) AS
(
SELECT @MinDate as startdate, @MinDate + @intval - .0000001 as enddate, @intval as intervl
UNION ALL
SELECT startdate + intervl as startdate, enddate + intervl as enddate, intervl as intervl
FROM times
WHERE startdate + intervl <= @MaxDate
)
select startdate, enddate from times;
go

I was then going to call it by using the below but my problem is, me.DateEnded is outside of dr.enddate and therefore the occurance would be '0':

SELECT dr.startdate, dr.enddate, count(me.DateStarted) as occurrence
FROM fn_daterange('16/07/2012 08:30', '16/07/2012 13:30', '00:30:00' ) dr
LEFT OUTER JOIN tbl_Bookings me
ON me.DateStarted BETWEEN dr.startdate AND dr.enddate
AND me.DateEnded BETWEEN dr.startdate AND dr.enddate)
GROUP BY dr.startdate, dr.enddate

Can anyone possibly suggest a better way of doing this or hopefully offer a solution to the way I'm trying to do it currently?

Thanks in advance!

Answer 1

I believe I have a working solution in SQL. This assumes that the data in tbl_Bookings is consistent, ie no start/end times overlap for a given room. Probably a simpler way, but the trick was to order the bookings and pair up end times with the following start times. There are two extra queries unioned on to get any intervals after your specified Start but before the first booking. Likewise for End.

EDIT: Added WHERE NOT EXISTS guards to the last two queries in case @Start or @End fell within a booked interval.

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

-- Show any available time from @Start until the next DateStarted, unless @Start 
-- falls within a booked interval.
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

-- Show any available time from the last DateEnded to @End, unless @End 
-- falls within a booked interval.
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

Working SqlFiddle