0

Is there a way to generate list of missing dates in table in Oracle?

Input

name,my_date
A,04-JAN-2000
A,05-JAN-2000
A,08-JAN-2000
A,08-JAN-2000  -- duplicates possible
A,10-JAN-2000
B,09-FEB-2001
B,10-FEB-2001
B,05-FEB-2001

Result

A,06-JAN-2000
A,07-JAN-2000
A,09-JAN-2000
B,06-FEB-2001
B,07-FEB-2001
B,08-FEB-2001

After suggestion from @diiN__________ to see Oracle: select missing dates, I managed to get it working for a specific name as follows:

WITH all_dates_wo_boundary_values as
(SELECT oldest + level my_date
    FROM (SELECT MIN(my_date) oldest
                ,MAX(my_date) recent
             FROM mytable my
             WHERE my.name = 'A'
         )
 connect by level <= recent - oldest - 1
)
 SELECT my_date
FROM all_dates_wo_boundary_values
MINUS
SELECT my_date
FROM mytable my
WHERE my.name = 'A'

How could it be done for multiple names at once?

aeiou
  • 337
  • 1
  • 7

1 Answers1

4

For multiple names, you can use the LEAD analytic function to find the next date and then CROSS JOIN LATERAL (available from Oracle 12) a row-generator to generate the missing values:

SELECT t.name,
       m.missing
FROM   (
         SELECT name,
                dt,
                LEAD(dt) OVER (PARTITION BY name ORDER BY dt) AS next_dt
         FROM   table_name
       ) t
       CROSS JOIN LATERAL (
         SELECT dt + LEVEL AS missing
         FROM   DUAL
         WHERE  dt + 1 < next_dt
         CONNECT BY dt + LEVEL < next_dt
       ) m

Which, for the sample data:

CREATE TABLE table_name (Name,dt) AS
  SELECT 'A', DATE '2000-01-04' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-05' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-08' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-08' FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-10' FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-05' FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-09' FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-10' FROM DUAL;

Outputs:

NAME MISSING
A 06-JAN-00
A 07-JAN-00
A 09-JAN-00
B 06-FEB-01
B 07-FEB-01
B 08-FEB-01

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117