clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799078 fiddles created (41787 in the last week).

CREATE TABLE EventsTBL ( PID INT, EID INT, StartDate DATETIME, EndDate DATETIME );
 hidden batch(es)


INSERT INTO EventsTBL VALUES (13579, 1, N'2018-01-01T00:00:00', N'2019-03-31T00:00:00'), (13579, 2, N'2018-02-01T00:00:00', N'2018-05-31T00:00:00'), (13579, 2, N'2018-07-01T00:00:00', N'2019-01-31T00:00:00'), (13579, 7, N'2018-03-01T00:00:00', N'2019-03-31T00:00:00'), (13579, 5, N'2018-02-01T00:00:00', N'2018-04-30T00:00:00'), (13579, 5, N'2018-10-01T00:00:00', N'2019-03-31T00:00:00'), (13579, 8, N'2018-01-01T00:00:00', N'2018-04-30T00:00:00'), (13579, 8, N'2018-06-01T00:00:00', N'2018-12-31T00:00:00'), (13579, 13, N'2018-01-01T00:00:00', N'2019-03-31T00:00:00'), (13579, 6, N'2018-04-01T00:00:00', N'2018-05-31T00:00:00'), (13579, 6, N'2018-09-01T00:00:00', N'2018-11-30T00:00:00'), (13579, 4, N'2018-02-01T00:00:00', N'2019-01-31T00:00:00'), (13579, 19, N'2018-03-01T00:00:00', N'2018-07-31T00:00:00'), (13579, 19, N'2018-10-01T00:00:00', N'2019-02-28T00:00:00'), (13570, 16, N'2018-02-01T00:00:00', N'2018-06-30T00:00:00'), (13570, 16, N'2018-08-01T00:00:00', N'2018-08-31T00:00:00'), (13570, 16, N'2018-10-01T00:00:00', N'2019-02-28T00:00:00'), (13570, 23, N'2018-03-01T00:00:00', N'2018-06-30T00:00:00'), (13570, 23, N'2018-11-01T00:00:00', N'2019-01-31T00:00:00');
19 rows affected
 hidden batch(es)


-- -- 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;
PID EID Source ScheduleDate
13570 16 StartDate 2018-02-01 00:00:00.000
13570 23 StartDate 2018-03-01 00:00:00.000
13570 23 EndDate 2018-06-30 00:00:00.000
13570 16 EndDate 2018-06-30 00:00:00.000
13570 16 StartDate 2018-08-01 00:00:00.000
13570 16 EndDate 2018-08-31 00:00:00.000
13570 16 StartDate 2018-10-01 00:00:00.000
13570 23 StartDate 2018-11-01 00:00:00.000
13570 23 EndDate 2019-01-31 00:00:00.000
13570 16 EndDate 2019-02-28 00:00:00.000
13579 13 StartDate 2018-01-01 00:00:00.000
13579 1 StartDate 2018-01-01 00:00:00.000
13579 8 StartDate 2018-01-01 00:00:00.000
13579 5 StartDate 2018-02-01 00:00:00.000
13579 2 StartDate 2018-02-01 00:00:00.000
13579 4 StartDate 2018-02-01 00:00:00.000
13579 19 StartDate 2018-03-01 00:00:00.000
13579 7 StartDate 2018-03-01 00:00:00.000
13579 6 StartDate 2018-04-01 00:00:00.000
13579 5 EndDate 2018-04-30 00:00:00.000
13579 8 EndDate 2018-04-30 00:00:00.000
13579 2 EndDate 2018-05-31 00:00:00.000
13579 6 EndDate 2018-05-31 00:00:00.000
13579 8 StartDate 2018-06-01 00:00:00.000
13579 2 StartDate 2018-07-01 00:00:00.000
13579 19 EndDate 2018-07-31 00:00:00.000
13579 6 StartDate 2018-09-01 00:00:00.000
13579 19 StartDate 2018-10-01 00:00:00.000
13579 5 StartDate 2018-10-01 00:00:00.000
13579 6 EndDate 2018-11-30 00:00:00.000
13579 8 EndDate 2018-12-31 00:00:00.000
13579 2 EndDate 2019-01-31 00:00:00.000
13579 4 EndDate 2019-01-31 00:00:00.000
13579 19 EndDate 2019-02-28 00:00:00.000
13579 13 EndDate 2019-03-31 00:00:00.000
13579 7 EndDate 2019-03-31 00:00:00.000
13579 1 EndDate 2019-03-31 00:00:00.000
13579 5 EndDate 2019-03-31 00:00:00.000
 hidden batch(es)