User Edit Log

DECLARE @StartDate DATETIME = '2/16/2025';
DECLARE @EndDate DATETIME = '2/22/2025';
DECLARE @ExcludedUsernames NVARCHAR(MAX) = 'AUTOCOMP,ESSUSER,INVALIDWG,SPLPAY,VOUCHER,BLENDOTCRD,CERTOTCRD,AUTOSVC';
--list exclude users
DECLARE @EMPEdits NVARCHAR(MAX) = '98,15,700';
-- List of employee edit types to exclude

WITH
    EditTypeDescriptions
    AS
    (
        SELECT *
        FROM (VALUES
                (601, 'Incident Adjustment (PAYDESNUM=NEW incident #, PREVPAYDES=ORIGINAL incident #)'),
                (500, 'Historical Edit'),
                (2, 'Hours Credit'),
                (4, 'Credit Worked Time'),
                (603, 'Add Incident'),
                (403, 'Addressed Exception'),
                (404, 'EMP Change State Edit'),
                (301, 'Credit Benefit Balance'),
                (302, 'Debit Benefit Balance'),
                (303, 'New Balance'),
                (10, 'Authorized Timecard'),
                (0, 'Added Punch'),
                (1, 'Deleted Punch'),
                (12, 'Adjust Punch to Start of Shift'),
                (13, 'Adjust Punch to End of Shift'),
                (101, 'Workgroup Transfer at Time of Day'),
                (103, 'Paydes Transfer at Time of Day'),
                (114, 'Paydes Block Hours Transfer'),
                (23, 'Delete Schedule'),
                (22, 'Add Schedule'),
                (401, 'Worksheet Hours Entry'),
                (29, 'Add Benefit Schedule'),
                (119, 'Workgroup Transfer time of day'),
                (700, 'EMP Add trans'),
                (6, 'Pay to')
    ) AS T(EditType, Description)
    ),

    ExcludedUsers
    AS
    (
        SELECT value AS ExcludedUsername
        FROM STRING_SPLIT(@ExcludedUsernames, ',')
        -- Split usernames into a table
    ),
    ExcludedEdits
    AS
    (
        SELECT CAST(value AS INT) AS EditType
        FROM STRING_SPLIT(@EMPEdits, ',')
        -- Convert List of employee edit types list into a table
    ),

    Edits
    AS
    (
        -- Current Edits
                            SELECT
                CASE 
            WHEN SE.CANCEL <> 0 AND SE.CANCELBY IS NOT NULL THEN SE.CANCELBY  -- Use canceling user
            ELSE SE.USERNAME  
        END AS USERNAME,
                CASE 
            WHEN SE.CANCEL <> 0 AND SE.CANCELBY IS NOT NULL THEN UC.FRIENDLYNAME  -- Friendly name of canceling user
            ELSE U.FRIENDLYNAME  
        END AS UserFriendlyName,
                E.LASTNAME + ', ' + E.FIRSTNAME AS EmployeeName,
                CASE 
            WHEN SE.CANCEL <> 0 AND SE.CANCELBY IS NOT NULL THEN 'Cancelled A ' + COALESCE(ETD.Description, CAST(SE.EDITTYPE AS VARCHAR))
            WHEN SE.EDITTYPE = 6 AND SE.PAYDESNUM IS NOT NULL THEN 
                COALESCE(ETD.Description, CAST(SE.EDITTYPE AS VARCHAR)) + ' - ' + 
                CASE SE.PAYDESNUM
                    WHEN 1 THEN 'Sch Start'
                    WHEN 2 THEN 'Sch End'
                    WHEN 3 THEN 'Act Start'
                    WHEN 4 THEN 'Act End'
                    ELSE CAST(SE.PAYDESNUM AS VARCHAR)  -- Fallback for unknown values
                END                
            ELSE COALESCE(ETD.Description, CAST(SE.EDITTYPE AS VARCHAR))  
        END AS EditTypeDescription,
                FORMAT(SE.EFFDATE, 'MM/dd/yyyy') AS EFFDATE,
                CASE
            WHEN SE.EDITTYPE IN (6,22,23,29,403) THEN NULL
            ELSE FORMAT(SE.EFFTIME, 'hh:mm tt')
        END AS EFFTIME,
                CASE 
            WHEN SE.CANCEL <> 0 AND SE.CANCELBY IS NOT NULL THEN SE.USERNAME  -- Show original USERNAME
            WHEN SE.REASON = 0 THEN NULL
            ELSE COALESCE(R.DESCR, CAST(SE.REASON AS VARCHAR))  
        END AS ReasonDescription,
                CASE 
            WHEN SE.HOURS = 0 THEN NULL  
            ELSE CAST(SE.HOURS AS FLOAT) / 60 
        END AS HOURS,
                CASE 
            --WHEN SE.CANCEL <> 0 AND SE.CANCELBY IS NOT NULL THEN 'Canceled'  -- Mark as Canceled
            WHEN SE.EDITTYPE = 403 THEN EXC.NAME  -- Exception Name for EditType 403
            WHEN SE.EDITTYPE IN (29,301,302) AND SE.PREVPAYDES IS NOT NULL THEN B.NAME  -- Benefit Name for EditType 301, 302
            WHEN SE.EDITTYPE = 2 THEN PDS.NAME
            ELSE NULL
        END AS Description,
                CASE 
            WHEN SE.CANCEL <> 0 AND SE.CANCELBY IS NOT NULL THEN FORMAT(SE.CANCELON, 'MM/dd/yyyy hh:mm tt')  
            ELSE FORMAT(SE.TSDATETIME, 'MM/dd/yyyy hh:mm tt')  
        END AS FormattedTSDATETIME,
                'Current Edits' AS SourceTable
            FROM SUPEDITS SE
                LEFT JOIN USERS U ON SE.USERNAME = U.NAME
                LEFT JOIN EMPLOYEES E ON SE.FILEKEY = E.FILEKEY
                LEFT JOIN EditTypeDescriptions ETD ON SE.EDITTYPE = ETD.EditType
                LEFT JOIN BENEFITS B ON SE.EDITTYPE IN (29,301,302)
                    AND SE.PREVPAYDES IS NOT NULL
                    AND SE.PREVPAYDES = B.NUM
                LEFT JOIN PAYDESIGNATIONS PDS ON SE.EDITTYPE = 2
                    AND SE.PAYDESNUM IS NOT NULL
                    AND SE.PAYDESNUM = PDS.PAYDESNUM
                LEFT JOIN REASONS R ON SE.REASON = R.NUM -- FIXED: Explicit join on REASON
                LEFT JOIN ExcludedUsers EU ON SE.USERNAME = EU.ExcludedUsername
                LEFT JOIN EXCEPTIONS EXC ON SE.EDITTYPE = 403 AND SE.PAYDESNUM = EXC.NUM
                LEFT JOIN USERS UC ON SE.CANCELBY = UC.NAME
            WHERE SE.EFFDATE BETWEEN @StartDate AND @EndDate
                AND SE.EDITTYPE NOT IN (SELECT EditType
                FROM ExcludedEdits) -- Emp Edits
                AND (
        EU.ExcludedUsername IS NULL -- Regular filter: Exclude system users
                OR (SE.CANCEL <> 0 AND SE.CANCELBY IS NOT NULL)  -- Allow canceled edits
    )

        UNION ALL

            -- Historical Edits
            SELECT
                CASE 
            WHEN SH.CANCEL <> 0 AND SH.CANCELBY IS NOT NULL THEN SH.CANCELBY  
            ELSE SH.USERNAME  
        END AS USERNAME,
                CASE 
            WHEN SH.CANCEL <> 0 AND SH.CANCELBY IS NOT NULL THEN UC.FRIENDLYNAME  
            ELSE U.FRIENDLYNAME  
        END AS UserFriendlyName,
                E.LASTNAME + ', ' + E.FIRSTNAME AS EmployeeName,
                CASE 
            WHEN SH.CANCEL <> 0 AND SH.CANCELBY IS NOT NULL THEN 'Cancelled A ' + COALESCE(ETD.Description, CAST(SH.EDITTYPE AS VARCHAR))
            WHEN SH.EDITTYPE = 6 AND SH.PAYDESNUM IS NOT NULL THEN 
                COALESCE(ETD.Description, CAST(SH.EDITTYPE AS VARCHAR)) + ' - ' + 
                CASE SH.PAYDESNUM
                    WHEN 1 THEN 'Sch Start'
                    WHEN 2 THEN 'Sch End'
                    WHEN 3 THEN 'Act Start'
                    WHEN 4 THEN 'Act End'
                    ELSE CAST(SH.PAYDESNUM AS VARCHAR)
                END
            ELSE COALESCE(ETD.Description, CAST(SH.EDITTYPE AS VARCHAR))  
        END AS EditTypeDescription,
                FORMAT(SH.EFFDATE, 'MM/dd/yyyy') AS EFFDATE,
                CASE
            WHEN SH.EDITTYPE IN (6,22,23,29,403) THEN NULL
            ELSE FORMAT(SH.EFFTIME, 'hh:mm tt')
        END AS EFFTIME,
                CASE 
            WHEN SH.CANCEL <> 0 AND SH.CANCELBY IS NOT NULL THEN SH.USERNAME
            WHEN SH.REASON = 0 THEN NULL
            ELSE COALESCE(R.DESCR, CAST(SH.REASON AS VARCHAR))  
        END AS ReasonDescription,
                CASE 
            WHEN SH.HOURS = 0 THEN NULL  
            ELSE CAST(SH.HOURS AS FLOAT) / 60 
        END AS HOURS,
                CASE 
            --WHEN SH.CANCEL <> 0 AND SH.CANCELBY IS NOT NULL THEN 'Canceled'
            WHEN SH.EDITTYPE = 403 THEN EXC.NAME  -- Exception Name for EditType 403
            WHEN SH.EDITTYPE IN (29,301,302) AND SH.PREVPAYDES IS NOT NULL THEN B.NAME  -- Benefit Name for EditType 301, 302
            WHEN SH.EDITTYPE = 2 THEN PDS.NAME
            ELSE NULL 
        END AS Description,
                CASE 
            WHEN SH.CANCEL <> 0 AND SH.CANCELBY IS NOT NULL THEN FORMAT(SH.CANCELON, 'MM/dd/yyyy hh:mm tt')  
            ELSE FORMAT(SH.TSDATETIME, 'MM/dd/yyyy hh:mm tt')  
        END AS FormattedTSDATETIME,
                'Historical Edits' AS SourceTable
            FROM SUPEDITHIST SH
                LEFT JOIN USERS U ON SH.USERNAME = U.NAME
                LEFT JOIN EMPLOYEES E ON SH.FILEKEY = E.FILEKEY
                LEFT JOIN EditTypeDescriptions ETD ON SH.EDITTYPE = ETD.EditType
                LEFT JOIN BENEFITS B ON SH.EDITTYPE IN (29,301,302)
                    AND SH.PREVPAYDES IS NOT NULL
                    AND SH.PREVPAYDES = B.NUM
                LEFT JOIN PAYDESIGNATIONS PDS ON SH.EDITTYPE = 2
                    AND SH.PAYDESNUM IS NOT NULL
                    AND SH.PAYDESNUM = PDS.PAYDESNUM
                LEFT JOIN REASONS R ON SH.REASON = R.NUM -- FIXED: Explicit join on REASON
                LEFT JOIN ExcludedUsers EU ON SH.USERNAME = EU.ExcludedUsername
                LEFT JOIN EXCEPTIONS EXC ON SH.EDITTYPE = 403 AND SH.PAYDESNUM = EXC.NUM
                LEFT JOIN USERS UC ON SH.CANCELBY = UC.NAME
            WHERE SH.EFFDATE BETWEEN @StartDate AND @EndDate
                AND SH.EDITTYPE NOT IN (SELECT EditType
                FROM ExcludedEdits) --Emp edits
                AND (
        EU.ExcludedUsername IS NULL
                OR (SH.CANCEL <> 0 AND SH.CANCELBY IS NOT NULL)
    )

        UNION ALL

            -- User Activity Data
            SELECT
                U.NAME AS USERNAME,
                U.FRIENDLYNAME AS UserFriendlyName,
                E.LASTNAME + ', ' + E.FIRSTNAME AS EmployeeName,
                'User Activity' AS EditTypeDescription,
                FORMAT(UA.EFFDATE, 'MM/dd/yyyy') AS EFFDATE,
                NULL AS EFFTIME,
                NULL AS ReasonDescription,
                NULL AS HOURS,
                UA.DESCR AS Description,
                FORMAT(UA.EFFDATE, 'MM/dd/yyyy hh:mm tt') AS TSDATETIME,
                'User Activity' AS SourceTable
            FROM USERACTIVITY UA
                LEFT JOIN USERS U ON UA.USERID = U.UNIQUEID
                LEFT JOIN EMPLOYEES E ON UA.FILEKEY = E.FILEKEY
            WHERE UA.EFFDATE BETWEEN @StartDate AND @EndDate
    ),

    RankedEdits
    AS
    (
        SELECT *,
            ROW_NUMBER() OVER (
               PARTITION BY USERNAME, EmployeeName, EditTypeDescription, EFFDATE, EFFTIME, ReasonDescription, HOURS, Description
               ORDER BY CASE WHEN SourceTable = 'Current Edits' THEN 1 ELSE 2 END
           ) AS RowNum
        FROM Edits
    )

SELECT
    USERNAME,
    UserFriendlyName,
    EmployeeName,
    -- Concatenated Description field
    EditTypeDescription + 
    ' | Date: ' + EFFDATE +
    CASE 
        WHEN EFFTIME IS NOT NULL THEN ' | Time: ' + EFFTIME 
        ELSE '' 
    END +
    CASE 
        WHEN HOURS IS NOT NULL THEN ' | Hours: ' + CAST(ROUND(HOURS, 2) AS VARCHAR)
        ELSE '' 
    END +
    CASE 
        WHEN Description IS NOT NULL THEN ' | ' + Description 
        ELSE ''
    END +
    CASE
        WHEN ReasonDescription = USERNAME THEN ' | Original User: ' + USERNAME --Doesnt currently work, Need to come back to.
        WHEN ReasonDescription IS NOT NULL THEN ' | Reason: ' + ReasonDescription 
        ELSE '' 
    END AS Description,
    FormattedTSDATETIME AS TSDATETIME,
    SourceTable
FROM RankedEdits
WHERE RowNum = 1
ORDER BY USERNAME, EmployeeName, EFFDATE;