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;
