Missing Periods Benefit Schedule to Pay

SET NOCOUNT ON;

DECLARE @FILEKEY INT = 2299;
-- Replace with specific employee's FILEKEY

-- Get employee info for date filtering
WITH
    EmployeeInfo
    AS
    (
        SELECT
            @FILEKEY AS FILEKEY,
            e.ACTIVESTATUSEFFDATE,
            DATEADD(WEEK, -1, 
            DATEADD(DAY, -(DATEPART(WEEKDAY, e.ACTIVESTATUSEFFDATE) - 1), 
                CAST(e.ACTIVESTATUSEFFDATE AS DATE))
        ) AS FirstValidPeriodStart
        FROM EMPLOYEES e
        WHERE e.FILEKEY = @FILEKEY
            AND e.ACTIVESTATUS = 0
    )
-- Show all schedules and their payment status
SELECT
    s.FILEKEY,
    s.SCHDATE,
    s.STARTTIME,
    s.ENDTIME,
    s.HOURS AS SCHEDULED_HOURS,
    CASE 
        WHEN s.SCHTYPE = 0 THEN 'NORMAL'
        WHEN s.SCHTYPE = 1 THEN 'FLEX'
        WHEN s.SCHTYPE = 2 THEN 'BENEFIT'
        ELSE 'UNKNOWN'
    END AS SCHEDULE_TYPE,
    s.WG1,
    s.WG2,
    s.WG3,
    s.RATE AS SCHEDULED_RATE,
    s.BENEFIT,
    s.PATTERN,
    s.SCHSTYLE,
    s.WG4,
    s.WG5,
    p.PAYDESNUM,
    p.HOURS AS PAID_HOURS,
    p.DOLLARS AS PAID_DOLLARS,
    p.RATE AS PAID_RATE,
    CASE 
        WHEN p.FILEKEY IS NULL THEN 'NO PAY RECORD'
        ELSE 'HAS PAY RECORD'
    END AS PAY_STATUS,
    CASE 
        WHEN p.FILEKEY IS NULL THEN s.HOURS * s.RATE
        ELSE 0
    END AS POTENTIAL_LOST_DOLLARS
FROM EmployeeInfo ei
    INNER JOIN SCHEDULES s ON ei.FILEKEY = s.FILEKEY
        AND s.SCHDATE >= ei.FirstValidPeriodStart
        AND s.SCHDATE > '2025-04-19' -- Only check after the issue started
        AND s.SCHDATE <= CAST(GETDATE() AS DATE) -- Only up to today
    LEFT JOIN PAYDESHIST p ON s.FILEKEY = p.FILEKEY
        AND s.SCHDATE = p.EVENTDATE
WHERE s.FILEKEY = @FILEKEY
ORDER BY s.SCHDATE;

SET NOCOUNT OFF;