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

CREATE TABLE EventsTBL ( PID INT, EID INT, StartDate DATETIME, EndDate DATETIME ); 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 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
PID StartDate EndDate
13570 01/03/2018 00:00:00 30/06/2018 00:00:00
13579 01/04/2018 00:00:00 30/04/2018 00:00:00
13570 01/11/2018 00:00:00 31/01/2019 00:00:00
13579 01/10/2018 00:00:00 30/11/2018 00:00:00
 hidden batch(es)