Missing Benefits Insert (Safety Check)

SET NOCOUNT ON;

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

-- Safety check - show what would be inserted into both tables
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
    ),
    MissingBenefitPay
    AS
    (
        -- Find benefit schedules without matching pay records
        SELECT
            s.FILEKEY,
            s.SCHDATE,
            s.HOURS,
            s.RATE,
            s.WG1,
            s.WG2,
            s.WG3,
            s.WG4,
            s.WG5,
            s.BENEFIT,
            b.PAYDESNUM,
            b.NAME AS BENEFIT_NAME,
            s.HOURS * s.RATE AS CALCULATED_DOLLARS
        FROM EmployeeInfo ei
            INNER JOIN SCHEDULES s ON ei.FILEKEY = s.FILEKEY
                AND s.SCHDATE >= ei.FirstValidPeriodStart
                AND s.SCHDATE > '2025-04-19'
                AND s.SCHDATE <= CAST(GETDATE() AS DATE)
                AND s.SCHTYPE = 2 -- Only benefit schedules
            INNER JOIN BENEFITS b ON s.BENEFIT = b.NUM
            LEFT JOIN PAYDESHIST p ON s.FILEKEY = p.FILEKEY
                AND s.SCHDATE = p.EVENTDATE
                AND b.PAYDESNUM = p.PAYDESNUM
            LEFT JOIN BENHIST bh ON s.FILEKEY = bh.FILEKEY
                AND s.SCHDATE = bh.EVENTDATE
                AND s.BENEFIT = bh.BENEFIT
        WHERE p.FILEKEY IS NULL -- No matching pay record
            AND bh.FILEKEY IS NULL
        -- No matching benefit history record
    )
-- Show what would be inserted into PAYDESHIST
    SELECT
        'PAYDESHIST PREVIEW' AS TableName,
        FILEKEY,
        SCHDATE AS EVENTDATE,
        WG1,
        WG2,
        WG3,
        PAYDESNUM,
        RATE,
        HOURS / 60 AS PAYDESHIST_HOURS, -- Actual hours value
        CALCULATED_DOLLARS AS DOLLARS,
        WG4,
        WG5,
        BENEFIT_NAME
    FROM MissingBenefitPay

UNION ALL

    -- Show what would be inserted into BENHIST
    SELECT
        'BENHIST PREVIEW' AS TableName,
        FILEKEY,
        SCHDATE AS EVENTDATE,
        WG1,
        WG2,
        WG3,
        PAYDESNUM,
        RATE,
        HOURS / 60 AS BENHIST_HOURS, -- Same hours value
        CALCULATED_DOLLARS AS DOLLARS,
        BENEFIT AS WG4, -- Show BENEFIT field for BENHIST
        2 AS WG5, -- Show FLAGS default value
        BENEFIT_NAME
    FROM MissingBenefitPay
ORDER BY TableName, EVENTDATE;

SET NOCOUNT OFF;