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!