Time packing with joins, Sql Server

Time packing with joins

-- I thought this might be relatively fast but I don't think that's the case. But I think it does work
-- I'll probably forget so I should comment now that the CTEs come in pairs and alternate grouping on start/end time

create table T (id int not null, grp int not null, startTime datetime not null, endTime datetime not null);
insert into T (id, grp, startTime, endTime) values
    (1, 1, '01:00:00', '02:00:00'), (2, 1, '01:45:00', '02:15:00'), (3, 1, '2:07:00', '2:30:00'),
    (4, 1, '2:29', '5:00'), (5, 1, '4:00', '4:30'), (6, 1, '4:59', '5:01'), (7, 1, '5:00', '5:03'), (8, 1, '5:02' ,'5:05'),
    (9, 1, '5:04', '5:10'), (10, 1, '5:08', '5:20'),
    (11, 2, '6:00', '8:00'), (12, 3, '3:00', '4:00'), (13, 3, '3:30', '3:40');

with T0 as (
select  l.grp,
        case when r.startTime < l.startTime then r.startTime else l.startTime end  as startTime,
    max(case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end) as endTime
from T as l left outer join T as r
    on  r.grp = l.grp
        and  r.startTime <= l.endTime  and l.startTime <= r.endTime
        and (r.startTime  < l.startTime or l.startTime <= r.startTime and r.endTime < l.endTime)
group by l.grp, case when r.startTime < l.startTime then r.startTime else l.startTime end
),
T1 as (
select  l.grp,
    min(case when r.startTime < l.startTime then r.startTime else l.startTime end) as startTime,
        case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end  as endTime
from T0 as l left outer join T0 as r
    on       r.grp = l.grp
        and  r.startTime <= l.endTime  and l.startTime <= r.endTime
        and (r.startTime  < l.startTime or l.startTime <= r.startTime and r.endTime < l.endTime)
group by l.grp, case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end
),
T2 as (
select  l.grp,
        case when r.startTime < l.startTime then r.startTime else l.startTime end  as startTime,
    max(case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end) as endTime
from T1 as l left outer join T1 as r
    on       r.grp = l.grp
        and  r.startTime <= l.endTime  and l.startTime <= r.endTime
        and (r.startTime  < l.startTime or l.startTime <= r.startTime and r.endTime < l.endTime)
--where not exists (select 1 from T1 m where m.grp = l.grp and m.startTime < l.startTime and m.endTime > l.endTime)
group by l.grp, case when r.startTime < l.startTime then r.startTime else l.startTime end
),
T3 as (
select  l.grp,
    min(case when r.startTime < l.startTime then r.startTime else l.startTime end) as startTime,
        case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end  as endTime
from T2 as l left outer join T2 as r
    on       r.grp = l.grp
        and  r.startTime <= l.endTime  and l.startTime <= r.endTime
        and (r.startTime  < l.startTime or l.startTime <= r.startTime and r.endTime < l.endTime)
group by l.grp, case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end
),
T4 as (
select  l.grp,
        case when r.startTime < l.startTime then r.startTime else l.startTime end as startTime,
    max(case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end) as endTime
from T3 as l left outer join T3 as r
    on       r.grp = l.grp
        and  r.startTime <= l.endTime  and l.startTime <= r.endTime
        and (r.startTime  < l.startTime or l.startTime <= r.startTime and r.endTime < l.endTime)
group by l.grp, case when r.startTime < l.startTime then r.startTime else l.startTime end
),
T5 as (
select  l.grp,
    min(case when r.startTime < l.startTime then r.startTime else l.startTime end) as startTime,
        case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end as endTime
from T4 as l left outer join T4 as r
    on       r.grp = l.grp
        and  r.startTime <= l.endTime  and l.startTime <= r.endTime
        and (r.startTime  < l.startTime or l.startTime <= r.startTime and r.endTime < l.endTime)
group by l.grp, case when r.endTime   > l.endTime   then r.endTime   else l.endTime   end
)
select * from T4 as l
--where not exists (select 1 from T2 as m where m.grp = l.grp and m.startTime < l.startTime and m.endTime > l.endTime)


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development