How to fill date range gaps Oracle SQL-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

You are modeling date ranges incorrectly; an interval ending at midnight on 02-14-2021, for example, should not include 02-14-2021. In your model it does.

This leads to unnecessary complications in all the queries you write against your model. In the solution below I need to add 1 to end dates first, do all the processing, and then subtract 1 at the end.

with
  ranges (date_from, date_to) as (
    select to_date('01.01.2021 00:00:00','DD.MM.YYYY hh24:mi:ss'),
           to_date('31.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss')
    from   dual
    union  all
    select to_date('27.03.2021 00:00:00','DD.MM.YYYY hh24:mi:ss'),
           to_date('27.04.2021 00:00:00','DD.MM.YYYY hh24:mi:ss')
    from   dual 
    union  all
    select to_date('01.05.2021 00:00:00','DD.MM.YYYY hh24:mi:ss'),
           to_date('31.12.2021 00:00:00','DD.MM.YYYY hh24:mi:ss') 
    from   dual 
)
select first_missing, last_missing - 1 as last_missing
from   (
         select dt                          as first_missing, 
                lead(df) over (order by dt) as last_missing
         from   (select date_from, date_to   1 as date_to from ranges)
         match_recognize(
           order by date_from
           measures first(date_from) as df, max(date_to) as dt
           pattern  (a* b)
           define   a as max(date_to) >= next (date_from)
         )
       )
where  last_missing is not null
;

FIRST_MISSING       LAST_MISSING       
------------------- -------------------
28.04.2021 00:00:00 30.04.2021 00:00:00