tsql intesect between 2 datetime recordsets

Question

I have an appointments table with appointments for a number of 'resources' what i need to do is query that and return (for a particular resource) all free appointment slots across a date range.

i had thought the best way to approach this would be to generate a temp table of possible appointment times (as the length of appointment may be 30/60/90 minutes - the appointment length would be specified for the query.) and then select the intersect of those two recordsets. i.e. all of those - across the date range - where there are NOT appointments in the appointments table. thus returning all possible appointments for that resource.

or maybe just - again - generate the records of possible appointment datetimes, and then except the actual appointments already booked..?

unless of course someone can suggest an easier option.?

also not entirely sure how to generate the table of possibles ie a table with records for 2010-12-08 09:00, 2010-12-08 10:00, and so on (for 1 hr appointments)...

any ideas?

edit: have a vague idea on the possibles...

DECLARE @startDate DateTime
DECLARE @EndDate DateTime

set @startDate = '2010-12-08 09:00'
set @endDate = '2010-12-11 09:00';

with mycte as
(
   select cast(@startDate as datetime) DateValue
   union all
   select dateadd(mi,30,DateValue)
   from    mycte   
   where   DateValue <= @endDate
   and datepart(hh, dateadd(mi,30,DateValue)) Between 9 AND 16

)
select DateValue
from    mycte

Pero P. · Accepted Answer · 2010-12-08T10:28:46.220

This is a classic gaps and islands problem. It essentially a common problem where you need to identify missing values (gaps) in a sequence. Fortunately there is a free sample chapter on this very topic from the Manning book, SQL Server MVP Deep Dives. Hopefully it will provide inspiration as it gives guidance on a number of possible approaches.

http://www.manning.com/nielsen/SampleChapter5.pdf

Here is Itzik Ben-Gan's description of the problem, quoted from the above chapter.

Gaps and islands problems involve missing values in a sequence ... The sequences involved can also be temporal, such as order dates, some of which are missing due to inactive periods (weekends, holidays). Finding periods of inactivity is an example of the gaps problem, and finding periods of activity is an example of the islands problem.

tsql intesect between 2 datetime recordsets

1 Answers1