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?

CodePudding user response：

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