missing_date day_of_week
2025-01-09 Thursday
2025-02-12 Wednesday
2025-02-19 Wednesday
2025-02-20 Thursday
2025-02-21 Friday
2025-07-04 Friday
2025-07-17 Thursday
WITH RECURSIVE all_weekdays AS (
    -- Generates all dates from 01/01/2025 to 08/31/2025
    SELECT DATE('2025-01-01') AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM all_weekdays
    WHERE dt < '2025-08-31'
),

all_weekdays_in_range AS (
    -- Filters for only weekdays (Monday to Friday) from the generated list
    SELECT
        dt AS missing_date,
        DAYNAME(dt) AS day_of_week
    FROM all_weekdays
    WHERE DAYOFWEEK(dt) IN (2, 3, 4, 5, 6)
),

-- **Define your holiday dates here**
holidays AS (
    SELECT '2025-01-01' AS holiday_date  -- Example: New Years Day
    UNION ALL
    SELECT '2025-01-20' AS holiday_date  -- Example: Martin Luther King Jr. Day
    UNION ALL
    SELECT '2025-02-17' AS holiday_date  -- Example: Presidents' Day
    UNION ALL
    SELECT '2025-04-18' AS holiday_date  -- Example: Good Friday
    UNION ALL
    SELECT '2025-05-26' AS holiday_date  -- Example: Memorial Day
    UNION ALL
    SELECT '2025-06-19' AS holiday_date  -- Example: Juneteenth
    UNION ALL
    SELECT '2025-11-28' AS holiday_date  -- Example: Thanksgiving
     UNION ALL
    SELECT '2025-09-01' AS holiday_date  -- Example: Labor Day
    Union All
    SELECT '2025-12-025' AS holiday_date  -- Example: Christmas Day
   
    -- Add more holidays here...
),

-- Gets all unique dates from your fidelity table, casting them to a DATE type
used_dates AS (
    SELECT DISTINCT CAST(fidCurDate AS DATE) AS fidCurDate
    FROM fidelity
    WHERE fidCurDate BETWEEN '2025-01-01' AND '2025-08-31'
)

-- Finds dates that are: 1) missing from the DB, AND 2) NOT a holiday
SELECT
  T1.missing_date,
  T1.day_of_week
FROM all_weekdays_in_range T1
LEFT JOIN used_dates T2
  ON T1.missing_date = T2.fidCurDate
WHERE
    T2.fidCurDate IS NULL
    AND T1.missing_date NOT IN (SELECT holiday_date FROM holidays)
ORDER BY T1.missing_date;