WITH all_dates AS (
SELECT DATE '2025-01-01' + LEVEL - 1 AS dt
FROM dual
CONNECT BY LEVEL <= (DATE '2026-01-01' - DATE '2025-01-01') + 1
),
existing_dates AS (
SELECT TRUNC(creation_date) AS dt
FROM XXCUST.CUSTOM_TL -- your table name
WHERE creation_date BETWEEN DATE '2025-01-01' AND DATE '2026-01-01'
)
SELECT a.dt AS missing_date
FROM all_dates a
LEFT JOIN existing_dates e
ON a.dt = e.dt
WHERE e.dt IS NULL
ORDER BY a.dt;
