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

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