Selecting overlapping time ranges

Question

T-SQL DateTime Question.

I have a set of time ranges. During those time ranges there could be a set of overlapping time ranges that I call 'blocked' out time. The blocked time wouldn't span more than one day. What I want to do is split the time to exclude the blocked out time, basically giving me the time ranges that are not 'blocked'. Its safe to assume that blocked times cant fall outside of the times ranges.

Example: I work 9am to 5pm with a 30 min lunch break at 1pm. I want the result of 2 rows: 9am to 1pm and 1.30pm to 5pm.

As mentioned, I have a set of time ranges so in the above example the working hours may differ on a daily basis and the number of breaks as well as their duration may differ.

I guess in terms of SQL the input parameters would look like this:

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'

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'

The result set would look like this.

Start                   End
---------------------   ---------------------
'01 Jan 2009 09:00:00' '01 Jan 2009 13:00:00'
'01 Jan 2009 13:30:00' '01 Jan 2009 17:00:00'
'02 Feb 2009 10:00:00' '02 Feb 2009 10:30:00'
'02 Feb 2009 11:00:00' '02 Feb 2009 12:00:00'
'02 Feb 2009 12:30:00' '02 Feb 2009 13:00:00'

I could do this with a cursor or while loop but if someone could suggest how to do this without iteration that would be great - thanks.

Answer 1

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