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?
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