Selecting overlapping time ranges

Question

Hi - 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

Answer 2

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.

Answer 3

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

Answer 4

I thought i'd share the solution i finally settled on:

slight adjustment to the temp table in that i've added a StartDate field to both @timeranges and @blockedtimes

declare @timeranges table ( StartDate datetime, StartDateTime datetime, EndDateTime datetime ) 
declare @blockedtimes table ( StartDate datetime, StartDateTime datetime, EndDateTime datetime )

anyways seems simpler than some of the other anwser posted - cheers for everyones help :)

select 
 *
from
(
    -- first SELECT get start boundry
    select t.StartDateTime s, b.StartDateTime e
    from @timeranges t, @blockedtimes b
    where 
        -- same day and blocks overlaps timerange
        t.StartDate = b.StartDate and (t.StartDateTime <= b.EndDateTime and b.StartDateTime <= t.EndDateTime)
    and
        -- the following is the important bit for this SELECT   
        not exists (select 1 from @blockedtimes b2 where b2.StartDate = b.StartDate and b2.StartDateTime < b.StartDateTime)
union
    -- second SELECT get spikes ie middle
    select b1.EndDateTime s, b2.StartDateTime e
    from @timeranges t, @blockedtimes b1, @blockedtimes b2
    where 
        -- same day and blocks overlaps timerange
        t.StartDate = b1.StartDate and (t.StartDateTime <= b1.EndDateTime and b1.StartDateTime <= t.EndDateTime) 
    and 
        -- same day and blocks overlaps timerange
        t.StartDate = b2.StartDate and (t.StartDateTime <= b2.EndDateTime and b2.StartDateTime <= t.EndDateTime) 
    and 
        -- the following is the important bit for this SELECT
        b1.EndDateTime < b2.StartDateTime
union
    -- third SELECT get end boundry
    select b.EndDateTime s, t.EndDateTime e
    from @timeranges t, @blockedtimes b
    where 
        -- same day and blocks overlaps timerange
        t.StartDate = b.StartDate and (t.StartDateTime <= b.EndDateTime and b.StartDateTime <= t.EndDateTime)
    and 
        -- the following is the important bit for this SELECT
        not exists (select 1 from @blockedtimes b2 where b2.StartDate = b.StartDate and b2.StartDateTime > b.StartDateTime)
) t1