How to calculate overlapping date range in SQL

Question

I am trying to identify date range from a list of over lapping date ranges. For example my date range is below.

START_DATE  END_DATE
01-SEP-19   04-OCT-19
01-OCT-19   30-OCT-19
05-OCT-19   20-OCT-19
31-OCT-19   15-NOV-19

From here i would like to calculate the overlapping date range and from there individual pockets of date range. My desired output is below

START_DATE  END_DATE
01-SEP-19   30-SEP-19
01-OCT-19   04-OCT-19
05-OCT-19   20-OCT-19
21-OCT-19   30-OCT-19
31-OCT-19   15-NOV-19

Any suggestion how to proceed with this would be helpful.

Testing in Oracle 13

Answer 1

You are including the end date in the date range. That only works if the end date is considered to be a date range in itself, going from midnight to midnight. Since Oracle "dates" include a time component, I strongly recommend thinking of an Oracle "date" as a point in time. In that case, it is much better to say that the end datetime is not included in the range. The SQL standard on Temporal Validity follows this approach.

As a bonus, using "exclusive" end dates makes problems like this easier.

I work around this by adding 1 to the end date at the beginning of the query, and subtracting 1 before ending the query. See https://stewashton.wordpress.com/2018/11/26/ranges-with-nulls-05-segments/ for an explanation and for what to do with NULLs in the date ranges.

create table t(START_DATE, END_DATE) as
select to_date('01-SEP-19','dd-mon-yy'), to_date('04-OCT-19','dd-mon-yy') from dual union all
select to_date('01-OCT-19','dd-mon-yy'), to_date('30-OCT-19','dd-mon-yy') from dual union all
select to_date('05-OCT-19','dd-mon-yy'), to_date('20-OCT-19','dd-mon-yy') from dual union all
select to_date('31-OCT-19','dd-mon-yy'), to_date('15-NOV-19','dd-mon-yy') from dual;

with unpivoted as (
  select * from (
    select start_date, end_date + 1 end_date from t
  )
  unpivot(start_date for seg_start in(start_date as 1, end_date as -1))
)
select start_date, end_date - 1 end_date, NUM_SEGS
from (
  select u.*,
  lead(start_date) over(order by start_date) end_date,
  sum(seg_start) over(order by start_date) num_segs
  from unpivoted u
)
where num_segs > 0 and start_date < end_date;

START_DATE END_DATE     NUM_SEGS
---------- ---------- ----------
2019-09-01 2019-09-30          1
2019-10-01 2019-10-04          2
2019-10-05 2019-10-20          2
2019-10-21 2019-10-30          1
2019-10-31 2019-11-15          1

Best regards, Stew Ashton

Answer 2

You can use the following query:

-- Your Test data:
WITH r(START_DATE,  END_DATE)  AS 
  (SELECT date'2019-09-01', date'2019-10-04' from dual union all
   SELECT date'2019-10-01', date'2019-10-30' from dual union all
   SELECT date'2019-10-05', date'2019-10-20' from dual union all
   SELECT date'2019-10-31', date'2019-11-15' from dual)
   , start_dates AS (SELECT start_date FROM r
                      UNION
                     -- Add the end dates that lie within one of the given ranges:
                     SELECT end_date + 1 FROM r e
                      WHERE EXISTS (SELECT 1 FROM r 
                                     WHERE e.end_date + 1  BETWEEN r.start_date AND r.end_date))
SELECT s.start_date
     , LEAST (-- Get the minimal end_date where the start_date is within the range
              (SELECT MIN(end_date) FROM r
                WHERE s.start_date BETWEEN r.start_date AND r.end_date)
              -- If there is start_date -1 prior to this end_date take this instead
              ,NVL((SELECT MIN(start_date) - 1 FROM r
                     WHERE s.start_date < r.start_date)
                  ,DATE'9999-12-31')) AS END_DATE
  FROM start_dates s

Result:

START_DATE  END_DATE
01-SEP-19   30-SEP-19
01-OCT-19   04-OCT-19
05-OCT-19   20-OCT-19
21-OCT-19   30-OCT-19
31-OCT-19   15-NOV-19

Answer 3

This will provide the date ranges i believe. And after this date range is found we can do another left join with the main table to identify which (start or end date) falls under these date ranges.

create table date_range
as 
select * 
from  
( 
    select "START_DATE" as dt
    from "CUST_name" 
    union
    select "END_DATE" 
    from "CUST_name" 
    union 
    select "START_DATE"
    from "CUST_PH" 
    union
    select "END_DATE" 
    from "CUST_PH" 
) x 
order by dt

select dt, nvl((LEAD (dt,1) OVER (ORDER BY dt)) -1, date '9999-12-31') AS next_dt
from date_range order by dt

DT  NEXT_DT
01-SEP-19   30-SEP-19
01-OCT-19   03-OCT-19
04-OCT-19   04-OCT-19
05-OCT-19   19-OCT-19
20-OCT-19   29-OCT-19
30-OCT-19   30-OCT-19
31-OCT-19   14-NOV-19
15-NOV-19   31-DEC-99

Answer 4

You can try the below query for overlapping date range in SQL:

 select * from t t1
          join t t2 on 
             (t1.START_DATE > t2.START_DATE and t1.START_DATE < t2.END_DATE  )
          or (t1.END_DATE   > t2.START_DATE and t1.END_DATE   < t2.END_DATE  )
          or (t1.END_DATE   > t2.END_DATE   and t1.START_DATE < t2.START_DATE)