WITH DistinctCounts
AS (SELECT EventCount = COUNT(DISTINCT EID),
PID
FROM EventsTBL
GROUP BY PID),
T1([PID], EID, Date, Flag)
AS (SELECT [PID],
EID,
StartDate,
1
FROM EventsTBL
UNION ALL
SELECT [PID],
EID,
EndDate,
-1
FROM EventsTBL),
T2
AS (SELECT *,
ActiveCount = SUM(Flag) OVER (PARTITION BY [PID] ORDER BY Date ROWS UNBOUNDED PRECEDING)
FROM T1),
T3
AS (SELECT *,
PrevActiveCount = LAG(ActiveCount, 1, -1) OVER (PARTITION BY [PID] ORDER BY Date)
FROM T2),
T4
AS (SELECT T3.*,
RN = ROW_NUMBER()
OVER (
PARTITION BY T3.[PID]
ORDER BY T3.Date) - 1
FROM T3
WHERE ActiveCount <> PrevActiveCount
AND EXISTS (SELECT *
FROM DistinctCounts DC
WHERE DC.PID = T3.PID
AND DC.EventCount IN ( T3.ActiveCount, T3.PrevActiveCount )))
SELECT PID,
StartDate = MIN(Date),
EndDate = MAX(Date)
FROM T4
GROUP BY PID,
RN / 2
