Sql-server – Find the OverLapped (Common) Date Range from Group of Date Ranges

datetimegaps-and-islandssql server

How can I find the Overlapped (common date range) from a group of Date Ranges given?

The following table contains events and programmes (EID and PID respectively)

CREATE TABLE #EventsTBL
(
    PID INT,
    EID INT,
    StartDate DATETIME,
    EndDate DATETIME
);

The below insert statements set up the example data

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');

The programme with PID=13570 just has two distinct event types and 5 event sessions. I need to know the time periods that both of Event 16 and 23 were active for that PID.

+-------+------------+------------+
|  PID  | StartDate  |  EndDate   |
+-------+------------+------------+
| 13570 | 2018-03-01 | 2018-06-30 |
| 13570 | 2018-11-01 | 2019-01-31 |
+-------+------------+------------+

And similarly for PID = 13579. – This has a greater number of event types to deal with but I still need to know the time ranges where all events for that programme are running concurrently.

+-------+------------+------------+
|  PID  | StartDate  |  EndDate   |
+-------+------------+------------+
| 13579 | 2018-04-01 | 2018-04-30 |
| 13579 | 2018-10-01 | 2018-11-30 |
+-------+------------+------------+

See the image below if that explanation is unclear

OverLapped (Common) Date Ranges

Best Answer

Something along the following lines should do what you need (DEMO)

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