How to fill date range gaps Oracle SQL

Question

With a given dataset:

WITH ranges AS (
select to_date('01.01.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_from,
    to_date('31.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_to 
from dual 
union
select to_date('27.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_from,
    to_date('27.04.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_to
from dual 
union
select to_date('01.05.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_from,
    to_date('31.12.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') date_to 
from dual 
)
SELECT * FROM ranges;

How to find the gap 28.04.2021-30.04.2021.? Also consider that there can be multiple gaps in between and ranges can overlap.

Any suggestion?

Answer 1

Try this query, tune to your needs:

WITH steps AS (
  SELECT date_from as dt, 1 as step FROM ranges
   UNION ALL
  SELECT date_to as dt, -1 as step FROM ranges
)
SELECT dt as dt_from,
       lead(dt) over (order by dt) as dt_to,
       sum(step) over (order by dt) as cnt_ranges
  FROM steps;


dt_from                 | dt_to                   | cnt_ranges
------------------------+-------------------------+-----------
2021-01-01 00:00:00.000 | 2021-03-27 00:00:00.000 | 1
2021-03-27 00:00:00.000 | 2021-03-31 00:00:00.000 | 2
2021-03-31 00:00:00.000 | 2021-04-27 00:00:00.000 | 1
2021-04-27 00:00:00.000 | 2021-05-01 00:00:00.000 | 0
2021-05-01 00:00:00.000 | 2021-12-31 00:00:00.000 | 1
2021-12-31 00:00:00.000 |                         | 0