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
Best Answer
Something along the following lines should do what you need (DEMO)