Geek Answers Handbook

Overlap interval selection

Question T-SQL DateTime.

I have a set of time ranges. During these time ranges, there may be a set of overlapping time ranges that I call “locked” time. Blocked time will not exceed one day. What I want to do is split the time to rule out blocked time, basically giving me time ranges that are not "locked". It can be safely assumed that blocked times cannot fall outside the time range.

Example: I work from 9 a.m. to 5 p.m. with a 30-minute lunch break at 13:00. I want a result of 2 lines: from 9 am to 1 pm and from 13:30 to 17:00.

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

I assume that in terms of SQL, the input parameters will 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 will look as follows.

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.

+2
source share
4 answers

I thought I could share a solution that I finally decided:

Slight setup of the temp table so that I added the StartDate field for both @timeranges and @blockedtimes

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

, - , - :)

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
+1

, .
,

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
+2

, , : A) . ( , .), B) , " ", .

    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;

?

datetime 0 1, , (0, EndDateTime @timeranges StartDateTime @blockedtimes) (1 ) . , rk, row_number. CASE, CHARINDEX , ...

- 0 1: 0,1,0,1,0,1... (0,1) , . (rk-1)/2.

. - , , , , . , , , .

, .

, , , . , row_number , .

0
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

:

  • Timers never overlap other timers
  • Blocked times never overlap other blocked times.
0
source

More articles:


All Articles