Selecting overlapping time ranges

First cut, may have some issues, but I'll keep working on it.
Works for the given data, just need to try additional scenarios

declare @timeranges table ( StartDateTime datetime, EndDateTime datetime ) declare @blockedtimes table ( StartDateTime datetime, EndDateTime datetime ) insert into @timeranges select '01 Jan 2009 09:00:00', '01 Jan 2009 17:00:00' union select '02 Feb 2009 10:00:00', '02 Feb 2009 13:00:00' --union select '03 Feb 2009 10:00:00', '03 Feb 2009 15:00:00' insert into @blockedtimes select '01 Jan 2009 13:00:00', '01 Jan 2009 13:30:00' union select '02 Feb 2009 10:30:00', '02 Feb 2009 11:00:00' union select '02 Feb 2009 12:00:00', '02 Feb 2009 12:30:00' --build an ordered, time range table with an indicator --to determine which ranges are timeranges 'tr' --and which are blockedtimes 'bt' -- declare @alltimes table (row int, rangetype varchar(10), StartDateTime datetime, EndDateTime datetime ) insert into @alltimes select row_number() over (order by a.startdatetime), * from ( select 'tr' as rangetype ,startdatetime, enddatetime from @timeranges union select 'bt' as rangetype ,startdatetime, enddatetime from @blockedtimes )a --what does the data look like -- select * from @alltimes -- -- build up the results select --start time is either the start time of a timerange, or the end of a blockedtime case when at1.rangetype = 'tr' then at1.startdatetime when at1.rangetype = 'bt' then at1.enddatetime end as [Start], case --a time range followed by another time range : end time from the current time range when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr' then at1.enddatetime --a time range followed by nothing (last record) : end time from the currenttime range when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null then at1.enddatetime --a time range followed by a blockedtime : end time is start time of blocked time when at1.rangetype = 'tr' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt' then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row) --a blocked time followed by a blockedtime : end time is start time of next blocked time when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'bt' then (select top 1 at2.startdatetime from @alltimes at2 where at2.row > at1.row and at2.rangetype = 'bt' order by row) --a blocked time followed by a time range : end time is end time of previous time range when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) = 'tr' then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc) --a blocked time followed by nothing (last record) : end time is end time of previous time range when at1.rangetype = 'bt' and (select at2.rangetype from @alltimes at2 where at2.row = at1.row+1) is null then (select top 1 at2.enddatetime from @alltimes at2 where at2.row < at1.row and at2.rangetype = 'tr' order by row desc) end as [End] from @alltimes at1 

Here's a solution that should work if two conditions hold for the input data: A) Each blocked time interval falls within a single time range interval. (You said this could be assumed.), and B) Blocked time intervals do not overlap - that is, no time is "doubly blocked" by falling in more than one blocked interval.

 with TB(src,S,E) as ( select 'T', StartDateTime, EndDateTime from @timeranges as T union all select 'B', StartDateTime, EndDateTime from @blockedtimes as B ), TBP(evt,switch,DT,rk) AS ( select src+DT, CHARINDEX(src+DT,'TEBSTSBE')/5 AS OffOn, EventDT, row_number() over ( order by EventDT, CHARINDEX(src+DT,'TEBSTSBE')/5 desc ) as rk from TB UNPIVOT ( EventDT FOR DT in ([S],[E]) ) as U ) select min(DT) as StartDateTime, max(DT) as EndDateTime from TBP group by (rk-1)/2 having min(DT) < max(DT) order by (rk-1)/2; 

How does this work?

It first tags all the datetime values with a 0 or 1 to indicate whether availability ends (0, for EndDateTime values in @timeranges and for StartDateTime values in @blockedtimes) or begins (1, for the other two possibilities) at the particular time. Then the times and tags are put in order by time, tag and numbered with a column rk that uses the row_number function. The tagging could be made more readable with a CASE expression, but CHARINDEX was less to type...

Because of the assumptions, the tag sequence will alternate between 0 and 1: 0,1,0,1,0,1..., with each consecutive (0,1) pair indicating the start and end of an interval of availability. These intervals can be numbered with (rk-1)/2.

The rows are grouped over each interval of availability. The minimum datetime in the group is the start time, and the maximum is the end time, and if these are different, the group represents a non-empty interval that belongs in the result set. Note that for your data, there are no empty intervals, but there would be if two blocked times abutted or if a blocked time ended simultaneously with a time range.

Finally, the results are pivoted for the display format you want.

It's not the easiest code to read, but it might be worth puzzling over. Solutions like this that use row_number and grouping are sometimes handy for solving tricky questions.

SELECT COALESCE(bt.StartDateTime, tr.StartDateTime), bt.EndDateTime FROM @timeranges tr CROSS APPLY ( SELECT bp.StartDateTime, bt.StartDateTime AS EndDateTime FROM ( SELECT StartDateTime FROM @blockedtimes bt WHERE bt.EndDateTime >= tr.StartDateTime AND bt.StartDateTime <= tr.EndDateTime UNION ALL SELECT tr.EndDateTime ) bt OUTER APPLY ( SELECT TOP 1 EndDateTime AS StartDateTime FROM @blockedtimes bti WHERE bti.EndDateTime >= tr.StartDateTime AND bti.StartDateTime <= tr.EndDateTime AND bti.StartDateTime < bt.StartDateTime ORDER BY bti.StartDateTime DESC ) AS bp ) bt 

This solutions relies on the following assumptions:

• Timeranges never overlap other timeranges
• Blocked times never overlap other blocked times