--
-- source: https://dba.stackexchange.com/questions/227345/find-the-overlapped-common-date-range-from-group-of-date-ranges/236947#236947
--
with
ProgramEvents (PID, EID) as (
select distinct q.PID, q.EID EID
from EventsTBL q
)
, SessionsSchedule (PID, EID, Source, ScheduleDate) as (
select PID, EID, Source, ScheduleDate
from EventsTBL
unpivot (ScheduleDate for Source in ( StartDate, EndDate )) u
)
, ConcurrentSessions (PID, IntersectionStartDate, IntersectionEndDate, concurrency, capacity) as (
select i.PID, i.IntersectionStartDate, i.IntersectionEndDate, count(distinct(e.EID)) concurrency, min(capacity) capacity
from (
select distinct s.PID, s.ScheduleDate IntersectionStartDate, min(e.ScheduleDate) IntersectionEndDate from (
select PID, ScheduleDate from SessionsSchedule
) s
inner join (
select PID, ScheduleDate from SessionsSchedule
) e on s.PID = e.PID
where coalesce(e.ScheduleDate, s.ScheduleDate) > s.ScheduleDate
group by s.PID, s.ScheduleDate
) i
cross apply (
select EID from EventsTBL t
where t.StartDate < i.IntersectionEndDate
and i.IntersectionStartDate < t.EndDate
and i.PID = t.PID
) e
cross apply (
select PID, count(EID) capacity
from ProgramEvents
where PID = i.PID
group by PID
) pe
group by i.PID, i.IntersectionStartDate, i.IntersectionEndDate
)
select PID, IntersectionStartDate StartTime, IntersectionEndDate EndTime
from ConcurrentSessions
where concurrency = capacity
order by PID, IntersectionStartDate;
PID
StartTime
EndTime
13570
2018-03-01 00:00:00.000
2018-06-30 00:00:00.000
13570
2018-11-01 00:00:00.000
2019-01-31 00:00:00.000
13579
2018-04-01 00:00:00.000
2018-04-30 00:00:00.000
13579
2018-10-01 00:00:00.000
2018-11-30 00:00:00.000
…
hidden batch(es)
with
-- Events available by program
ProgramEvents (PID, EID) as (
select distinct q.PID, q.EID EID
from EventsTBL q
)
select * from ProgramEvents
order by PID, EID;
PID
EID
13570
16
13570
23
13579
1
13579
2
13579
4
13579
5
13579
6
13579
7
13579
8
13579
13
13579
19
…
hidden batch(es)
with
-- Schedule listing the activities by session (session started/stopped)
SessionsSchedule (PID, EID, Source, ScheduleDate) as (
select PID, EID, Source, ScheduleDate
from EventsTBL
unpivot (ScheduleDate for Source in ( StartDate, EndDate )) u
)
select * from SessionsSchedule
order by PID, ScheduleDate;
