LOW Payroll Export (Version 2)

DECLARE @MIN DATE, @MAX DATE
SET @MIN={mindate}
SET @MAX={maxdate}

--------Parameters Section----------
DECLARE @HideRatesSalPayclass NVARCHAR(MAX) = '99'
DECLARE @PayClassFilter NVARCHAR(MAX) = '*'
DECLARE @TimeLocFilter NVARCHAR(MAX) = '*'
DECLARE @ExportingPDs NVARCHAR(MAX) = '1,2,3,4,5,7,9,10,11,12,13,16,18,19,20,21,22,23,24,25,26,27,30'
DECLARE @PDDecriptions NVARCHAR(MAX) = '3,4,5,7,9,10,11,13,16,18,19,20,21,22,23,24,25,26,27,30'
DECLARE @OnlyEvents BIT = 0 -- 0 = OFF, 1 = ON
DECLARE @MyEmployees BIT = 1 -- 0 = OFF, 1 = ON
DECLARE @SpecialRateDist NVARCHAR(MAX) = '*'
DECLARE @OT NVARCHAR(MAX) = '2,19'
DECLARE @SOT NVARCHAR(MAX) = '12'
DECLARE @DBL NVARCHAR(MAX) = '*'
DECLARE @ABS NVARCHAR(MAX) = '*'
DECLARE @CompE NVARCHAR(MAX) = '*'
DECLARE @CompP NVARCHAR(MAX) = '*'
DECLARE @Unpaid NVARCHAR(MAX) = '11,24'
DECLARE @HEvent NVARCHAR(MAX) = '13'


SELECT
    CONCAT(
        CASE WHEN RIGHT(E.IDNUM,1)='B' THEN LEFT(E.IDNUM,LEN(E.IDNUM)-1) ELSE E.IDNUM END,
        '-0',
        CONVERT(VARCHAR, pd.WG6)
    ) AS 'Pay Record ID',
    CASE
        WHEN PD.WG7 IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@SpecialRateDist, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'Regular'
        WHEN WG7.name LIKE '[*]%' THEN 'Event'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@OT, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'Overtime'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@SOT, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'STR OT'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@DBL, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'DoubleTime'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@ABS, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'Absence'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@CompE, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'CompTime (Earned)'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@CompP, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'CompTime Paid'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@Unpaid, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'Unpaid'
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@HEvent, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN 'Event'
        ELSE 'Regular'
    END AS 'Attendance Type',
    pd.EVENTDATE AS 'Attendance Date',
    NULL AS Absence,
    pd.HOURS AS 'Days/Hours',
    WG5.NAME AS 'Work Location',
    NULL AS 'Work Pay Group',
    NULL AS 'Comment',
    CASE 
        WHEN PD.WG7 IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@SpecialRateDist, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN NULL
        WHEN WG7.name LIKE '[*]%' THEN RIGHT(WG7.name, LEN(WG7.Name) - 1) 
        WHEN pd.PAYDESNUM IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@PDDecriptions, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN PDS.NAME
        ELSE NULL
    END AS 'Description',
    CASE 
        WHEN PD.WG7 IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@SpecialRateDist, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        ) THEN RIGHT(WG7.name, LEN(WG7.Name) - 1)
        ELSE NULL
    END AS 'Special Rate/Dist',
    CASE
        WHEN @HideRatesSalPayclass IS NOT NULL AND @HideRatesSalPayclass <> ''
        AND e.PAYCLASS IN (
                SELECT value
        FROM STRING_SPLIT(@HideRatesSalPayclass, ',')
            ) THEN NULL
        ELSE pd.RATE 
    END AS 'Special Rate',
    NULL AS 'Special Distribution'
FROM PAYDESHIST PD
    INNER JOIN EMPLOYEES E ON E.FILEKEY = PD.FILEKEY
    INNER JOIN WORKGROUP5 WG5 ON PD.WG5 = WG5.WGNUM
    INNER JOIN WORKGROUP7 WG7 ON PD.WG7 = WG7.WGNUM
    INNER JOIN PAYDESIGNATIONS PDS ON PD.PAYDESNUM = PDS.PAYDESNUM
WHERE pd.EVENTDATE BETWEEN @MIN AND @MAX
    AND pd.PAYDESNUM IN (
        SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@ExportingPDs, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
    )
    AND (
        @OnlyEvents = 0 -- If @OnlyEvents is OFF, ignore WG7.name check
    OR
    (
            WG7.name LIKE '[*]%' -- Always include WG7.name starting with *
    OR
    pd.PAYDESNUM IN (  -- Include PDs listed in @OT, @SOT, or @DBL
                SELECT TRY_CAST(value AS INT)
    FROM (
                                        SELECT value
            FROM STRING_SPLIT(@OT, ',')
            -- Split @OT
        UNION ALL
            SELECT value
            FROM STRING_SPLIT(@SOT, ',')
        -- Split @SOT
        UNION ALL
            SELECT value
            FROM STRING_SPLIT(@DBL, ',')  -- Split @DBL
                ) AS CombinedPDs
    WHERE TRY_CAST(value AS INT) IS NOT NULL  -- Ignore non-integer values (e.g., '*')
            )
        )
    )
    AND e.idnum != '999999999'
    AND e.ACTIVESTATUS=0
    AND (
        @TimeLocFilter = '*'
    OR pd.WG5 IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@TimeLocFilter, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        )
    )
    AND (
        @PayClassFilter = '*'
    OR e.payclass IN (
            SELECT TRY_CAST(value AS INT)
    FROM STRING_SPLIT(@PayClassFilter, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL
        )
    )
    AND (
        @MyEmployees = 0 -- If @MyEmployees is OFF, ignore employee access check
    OR
    (
            (SELECT COUNT(*)
    FROM USERACCESS U
    WHERE (U.USERID={userid}) AND (((U.WG1=E.WG1) OR (U.WG1=0)) AND ((U.WG2=E.WG2) OR (U.WG2=0)) AND ((U.WG3=E.WG3) OR (U.WG3=0)) AND ((U.WG4=E.WG4) OR (U.WG4=0)) AND ((U.WG5=E.WG5) OR (U.WG5=0)) AND ((U.WG6=E.WG6) OR (U.WG6=0)) AND ((U.WG7=E.WG7) OR (U.WG7=0))))>0
        )
    )
ORDER BY 1,3;