Punch Edits by Edit Date

Generates a comprehensive report of punch edits made within a specified date range, showing who made the edits, what type of edits were performed, and when they occurred. The report combines data from both active edits (SUPEDITS) and historical edits (SUPEDITHIST) to provide a complete audit trail.

Parameters

@MIN - Start date for the edit date range (example: '2023-01-01')
@MAX - End date for the edit date range (example: '2023-03-31')

Data Components

The report consists of the following main sections:

Active Edits (SUPEDITS) - Current punch edits within the date range
Historical Edits (SUPEDITHIST) - Historical punch edits not present in active edits
Combined Results - Union of both active and historical edits

Output Format

Column	Description
USER	Friendly name of the user who made the edit
EMP	Employee name (Last, First format)
EDITTYPE	Type of edit performed (Add Punch, Delete Punch, etc.)
PunchDate	Date of the punch being edited (M/d/yyyy format)
PunchTime	Time of the punch being edited (hh:mm tt format)
Edit Date	Date and time when the edit was made (M/d/yyyy hh:mm tt format)

Technical Implementation

The script uses:

Common Table Expressions (CTEs) to organize data from SUPEDITS and SUPEDITHIST
UNION ALL to combine active and historical edit records
CASE statements to translate edit type codes to readable descriptions
FORMAT functions for consistent date/time display
EXISTS clause to avoid duplicates between active and historical records

Edit Types

0 - Add Punch
1 - Delete Punch
12 - Alter Punch To Start of Shift
13 - Alter Punch To End of Shift

Notes

Results are ordered by User, Employee, and Edit Date
Useful for auditing punch modifications and tracking supervisor actions
Combines both current and historical edit data for comprehensive reporting
Date range filtering helps focus on specific time periods for analysis

T-SQL

punch_edits_by_edit_date.sql

DECLARE @MIN DATE, @MAX DATE;

SET @MIN = '2023-01-01';
SET @MAX = '2023-03-31';

/*
SET @MIN = {mindate};
SET @MAX = {maxdate};
*/

-- Define the filtered SUPEDITS data
WITH CTE_SUPEDITS AS (
    SELECT 
        S.FILEKEY,
        S.TSDATETIME,
        S.EDITTYPE,
        S.EFFDATE,
        s.EFFTIME,
        U.FRIENDLYNAME AS [USER],
        E.LASTNAME + ', ' + E.FIRSTNAME AS EMP
    FROM SUPEDITS S
    INNER JOIN USERS U ON S.USERNAME = U.NAME
    INNER JOIN EMPLOYEES E ON S.FILEKEY = E.FILEKEY
    WHERE 
        CAST(S.TSDATETIME AS DATE) BETWEEN @MIN AND @MAX
        AND S.EDITTYPE IN (0,1,12,13)
),
CTE_SUPEDITHIST AS (
    SELECT 
        SH.FILEKEY,
        SH.TSDATETIME,
        SH.EDITTYPE,
        SH.EFFDATE,
        SH.EFFTIME,
        U.FRIENDLYNAME AS [USER],
        E.LASTNAME + ', ' + E.FIRSTNAME AS EMP
    FROM SUPEDITHIST SH
    INNER JOIN USERS U ON SH.USERNAME = U.NAME
    INNER JOIN EMPLOYEES E ON SH.FILEKEY = E.FILEKEY
    WHERE 
        CAST(SH.TSDATETIME AS DATE) BETWEEN @MIN AND @MAX
        AND SH.EDITTYPE IN (0,1,12,13)
        AND NOT EXISTS (
            SELECT 1
            FROM SUPEDITS S
            WHERE S.FILEKEY = SH.FILEKEY
              AND S.TSDATETIME = SH.TSDATETIME
              AND S.EDITTYPE = SH.EDITTYPE
        )
)
-- Combine the results
SELECT 
    [USER],
    EMP,
    CASE 
        WHEN EDITTYPE = 0 THEN 'Add Punch'
        WHEN EDITTYPE = 1 THEN 'Delete Punch'
        WHEN EDITTYPE = 12 THEN 'Alter Punch To Start of Shift'
        WHEN EDITTYPE = 13 THEN 'Alter Punch To End of Shift'
    END AS EDITTYPE,
    FORMAT(EFFDATE, 'M/d/yyyy') AS PunchDate,
    FORMAT(EFFTIME, 'hh:mm tt') AS PunchTime,
    FORMAT(TSDATETIME, 'M/d/yyyy hh:mm tt') AS [Edit Date]
FROM CTE_SUPEDITS

UNION ALL

SELECT 
    [USER],
    EMP,
    CASE 
        WHEN EDITTYPE = 0 THEN 'Add Punch'
        WHEN EDITTYPE = 1 THEN 'Delete Punch'
        WHEN EDITTYPE = 12 THEN 'Alter Punch To Start of Shift'
        WHEN EDITTYPE = 13 THEN 'Alter Punch To End of Shift'
    END AS EDITTYPE,
    FORMAT(EFFDATE, 'M/d/yyyy') AS PunchDate,
    FORMAT(EFFTIME, 'hh:mm tt') AS PunchTime,
    FORMAT(TSDATETIME, 'M/d/yyyy hh:mm tt') AS [Edit Date]
FROM CTE_SUPEDITHIST

order by 1,2,6

Content Inventory

Doc File: content/docs/reports/users_information/punch_edits_by_edit_date.mdx
SQL Script: SQL/reports/users_information/punch_edits_by_edit_date.sql

Punch Edits by Edit Date

On this page