Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
store1
code
CTE Recursive sample
Running Total
Comercio
SALESPEOPLE
arithmetic
Shalvika's Query
SQL HW 1 Appline
Dynamic Sql command with output variable