clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798815 fiddles created (41842 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)


with SessionIntersections (PID, StartDate, EndDate) as ( select distinct s.PID, s.StartDate, min(e.EndDate) EndDate from ( select PID, StartDate from EventsTBL union select PID, EndDate from EventsTBL ) s inner join ( select PID, EndDate from EventsTBL union select PID, StartDate from EventsTBL ) e on e.EndDate > s.StartDate and s.PID = e.PID where s.StartDate is not null and e.EndDate is not null group by s.PID, s.StartDate ) , ConcurrentSessions (PID, StartDate, EndDate, concurrency, maxconcurrency) as ( select mcs.PID, StartDate, EndDate, concurrency, maxconcurrency from ( select i.PID, i.StartDate, i.EndDate, count(distinct(t.EID)) concurrency, min(maxconcurrency) maxconcurrency from SessionIntersections i inner join EventsTBL t on t.StartDate < i.EndDate and i.StartDate < t.EndDate and i.PID = t.PID inner join ( select q.PID, count(distinct(q.EID)) maxconcurrency from EventsTBL q group by q.PID ) mc on mc.PID = i.PID group by i.PID, i.StartDate, i.EndDate ) mcs ) select PID, StartDate, EndDate from ConcurrentSessions where concurrency = maxconcurrency order by PID, StartDate;
PID StartDate EndDate
13570 01/03/2018 00:00:00 30/06/2018 00:00:00
13570 01/11/2018 00:00:00 31/01/2019 00:00:00
13579 01/04/2018 00:00:00 30/04/2018 00:00:00
13579 01/10/2018 00:00:00 30/11/2018 00:00:00
 hidden batch(es)