Return date/times outside of a date/time range

[AdamGB]

Hi guys,

As if the title isn't confusing enough! I'm new here so hopefully someone can help me out and it certainly looks like a good community, so I'll endeavour to help out where I can too!

I'm stumped by something I've been asked to do recently and was hoping someone can point me in the right direction.

Basically my setup can be simplified into the following:
1 x Table which contains an auto-incrementing ID and 2 x Date/Time fields.

So an example of data stored in this could be:
ID | DateTime In | DateTime Out
1 | 10/07/2012 09:00 | 10/07/2012 10:00
2 | 10/08/2012 12:00 | 10/07/2012 13:00

Basically, I need to feed into this query 2 date times such as 10/07/2012 08:30 and 10/07/2012 13:30.

What I would like it to do is then search through my table and output times (each hour/half an hour) which it would view as 'available'.

For example, I feed in 10/07/2012 08:30 and 10/07/2012 13:30 and I would like then like it to output something like the following:
10/07/2012 08:30 - 10/07/2012 09:00
10/07/2012 10:00 - 10/07/2012 12:00
10/07/2012 13:00 - 10/07/2012 13:30

My question is, is this entirely possible using only SQL? Coming from a programmer perspective, this seems hard enough to do in code so I'm crossing my fingers that SQL can help me out here.

Does anyone have any suggestions as to the best way to tackle this?

Thanks in advance,
Adam

[skhanal]

Since you are trying to generate a list of time slots which does not exist in the table you are querying, you may have to build an intermediate table or inline view to accomplish this.

For example for the give time range, you can build an inline view with all time slots (make it half hour only to simplify it), then intersect this with the unavailable time slots data from the table.

Does the time slots start on the hour or half hour only?. If not it may complicate your query further. In that case it may be better to write a stored procedure instead.

[AdamGB]

Hi Skhanal,

The times would indeed always be on the hour or half hour.

Thanks,
Adam

[AdamGB]

I think I've 'nearly' sorted this but I'm still having troubles on how to best tackle it.

I'm using the below function to return the intervals between 2 dates. IE: if I enter 10/07/2012 08:00 and 10/07/2012 13:00, it returns
2012-07-10 08:00:00.000 2012-07-10 08:29:59.993
2012-07-10 08:30:00.000 2012-07-10 08:59:59.993
2012-07-10 09:00:00.000 2012-07-10 09:29:59.993
and so forth....

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
--************************************************** ************************
-- Procedure: fn_daterange()
-- Author: Ron Savage
-- Date: 12/16/2008
--
-- Description:
-- This function takes a starting and ending date and an interval, then
-- returns a table of all the dates in that range at the specified interval.
--
-- Change History:
-- Date Init. Description
-- 12/16/2008 RS Created.
-- ************************************************** ************************
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'm calling it using

select
dr.startdate,
dr.enddate,
count(me.eventdate) as eventcount
from
fn_daterange(convert(datetime, '10/07/2012 08:00:00', 103), convert(datetime,'10/07/2012 13:00:00', 103), '00:30:00' ) dr

LEFT OUTER JOIN test me
on ( me.eventdate between dr.startdate and dr.enddate)
group by
dr.startdate,
dr.enddate

The problem is, this only takes into account the eventdate, and not the end date that I want it to check. IE: if theres a booking in my test table which is starts at 10/07/2012 09:00 and ends on 10/07/2012 11:00, it will only show a eventcount of '1' for the times in 09:00.

I know how confusing that may be, but hopefully it'll make sense to someone! How can I take into consideration my endtime aswell as the 'eventdate' time?