1

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

  • yes, the row 2 doesn't have oct'18, nov'18, but the same eventid(eid=2) has another range (3rd row) from jul'18 to jan'19 which includes the oct'18, nov'18. Here, the Row 2,3 belong to eid(eventid) = 2. That implies event 2 has common date range of oct'18 and nov'18. – Thomas Newton Jan 17 at 11:10
3

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
  • Thanks Martin, you helped me a-lot really. It's working for many scenarios. Let me check for all types of Date Ranges and let You Know. Can you please let me know any alternative way of finding the same result set if any.? Because i need to check the performance aswell. – Thomas Newton Jan 17 at 18:27
  • Nope sorry I'm done here. I've improved your question so other people may understand it now and contribute alternatives – Martin Smith Jan 17 at 18:30
1

I present a solution for T-SQL.

Fiddle SQL Server 2017

Fiddle SQL Server 2017 ALT#1

The problem:

Find the date range intersections by program where sessions are active for all available events.

The solution:

Below I explain in 3 steps how to determine the intersections, the concurrency by intersection and the maximum concurency by program.

Step 1: query a continuous set of date intervals partitioned by program where either a session started or stopped.

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
)

Step 2: query the relevant date ranges where the concurrency (number of distinct events running simultaniously by program) equals the maximum concurrency (total number of distinct events available by program).

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
)

Step 3: output the date ranges where the maximum concurrency is reached

from ConcurrentSessions
where concurrency = maxconcurrency
order by PID, StartDate;

Full Query:

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;
  • Welcome to Database Administrators! Please explain how your query solves the author's problem; answers without explanation generally aren't received well. – Glorfindel Apr 29 at 15:35
  • Thank you. I hope to enjoy my stay here ;-) – user179304 Apr 29 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.