Find Missing Periods

WITH
    WeeklyPeriods
    AS
    (
        -- Generate all weekly periods from 4/20/2025 to current date
        SELECT
            DATEADD(WEEK, n, '2025-04-20') AS PERIODBEGIN,
            DATEADD(DAY, 6, DATEADD(WEEK, n, '2025-04-20')) AS PERIODEND
        FROM (
        SELECT TOP (DATEDIFF(WEEK, '2025-04-20', GETDATE()) + 1)
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
            FROM sys.all_objects a
        CROSS JOIN sys.all_objects b
    ) Numbers
    ),
    ActiveEmployees
    AS
    (
        -- Get active employees who have at least one record after 4/19/2025
        SELECT DISTINCT
            a.FILEKEY,
            e.ACTIVESTATUSEFFDATE,
            -- Calculate the start of the previous period based on hire date
            DATEADD(WEEK, -1, 
            DATEADD(DAY, -(DATEPART(WEEKDAY, e.ACTIVESTATUSEFFDATE) - 1), 
                CAST(e.ACTIVESTATUSEFFDATE AS DATE))
        ) AS FirstValidPeriodStart
        FROM ARCHIVES a
            INNER JOIN EMPLOYEES e ON a.FILEKEY = e.FILEKEY
        WHERE a.PERIODBEGIN > '2025-04-19'
            AND e.ACTIVESTATUS = 0
    ),
    ExpectedPeriods
    AS
    (
        -- Cross join active employees with weekly periods, but only for valid periods
        SELECT
            ae.FILEKEY,
            wp.PERIODBEGIN,
            wp.PERIODEND
        FROM ActiveEmployees ae
    CROSS JOIN WeeklyPeriods wp
        WHERE wp.PERIODBEGIN >= ae.FirstValidPeriodStart
    ),
    MissingPeriods
    AS
    (
        -- Find periods that should exist but don't
        SELECT
            ep.FILEKEY,
            ep.PERIODBEGIN,
            ep.PERIODEND
        FROM ExpectedPeriods ep
            LEFT JOIN ARCHIVES a ON ep.FILEKEY = a.FILEKEY
                AND ep.PERIODBEGIN = a.PERIODBEGIN
                AND ep.PERIODEND = a.PERIODEND
        WHERE a.FILEKEY IS NULL
            AND ep.PERIODBEGIN <= GETDATE()
        -- Only check periods up to current date
    ),
    EmployeesWithMissingPeriods
    AS
    (
        -- Group by employee and show missing periods
        SELECT
            FILEKEY,
            COUNT(*) AS MissingPeriodsCount,
            MIN(PERIODBEGIN) AS FirstMissingPeriod,
            MAX(PERIODEND) AS LastMissingPeriod
        FROM MissingPeriods
        GROUP BY FILEKEY
        HAVING COUNT(*) > 0
    )
-- Final result showing employees with missing periods
SELECT
    emp.FILEKEY,
    emp.MissingPeriodsCount,
    emp.FirstMissingPeriod,
    emp.LastMissingPeriod,
    -- Show detailed list of missing periods
    STUFF((
        SELECT ', ' + CONVERT(VARCHAR(10), mp.PERIODBEGIN, 101) + ' to ' + CONVERT(VARCHAR(10), mp.PERIODEND, 101)
    FROM MissingPeriods mp
    WHERE mp.FILEKEY = emp.FILEKEY
    ORDER BY mp.PERIODBEGIN
    FOR XML PATH('')
    ), 1, 2, '') AS MissingPeriodsList
FROM EmployeesWithMissingPeriods emp
ORDER BY emp.FILEKEY;