UKG Punch Export Report

Report that generates punch data in a format compatible with UKG (Ultimate Kronos Group) systems, extracting employee punch times for specific dates and formatting them as in/out pairs for payroll integration

Parameters

The script is currently configured with hardcoded dates:

Primary Date: '11/1/2023' - Main punch data extraction date
Secondary Date: '10/31/2023' - Additional punch data for first punch only

Note: These dates should be modified in the script before execution.

Data Components

The report consists of 3 main sections:

Employee Identification - Employee ID and external identifiers (mostly empty placeholders)
Punch Date Information - Formatted date information for the punch records
Punch Time Pairs - In and out punch times for each employee

Example Output

Punch Details Report Example

💡 Click the image above to zoom in and see the details more clearly!

Technical Implementation

The script uses:

Common Table Expressions (CTEs) - PunchesWithRowNum and ExpectedCounts for data organization
ROW_NUMBER() window function - To sequence punches by employee and date
LEAD() window function - To pair in punches with their corresponding out punches
PIVOT-like logic - Using modulo operation to identify in punches (odd row numbers)
Date formatting - FORMAT function for consistent date/time display
Complex JOIN conditions - Matching punches with expected transaction counts
ARCTRANSACTIONS and ARCHIVES tables - Source tables for punch and transaction data

T-SQL

UKGPUNCH.sql

WITH PunchesWithRowNum AS (
    SELECT
        FILEKEY,
		OWNERID,
		DTTMSTAMP as PunchDateTime,
        Format(DTTMSTAMP, 'HH:mm') AS PunchTime,
		CONVERT(DATE, DTTMSTAMP) AS PunchDate,
        ROW_NUMBER() OVER (PARTITION BY filekey, CONVERT(DATE, DTTMSTAMP) ORDER BY DTTMSTAMP) AS RowNum
    FROM
        ARCTRANSACTIONS
),
ExpectedCounts AS (
    SELECT
		UNIQUEID,
        FILEKEY,
        CONVERT(DATE, EVENTDATE) AS PunchDate,
        TRANSCOUNT
    FROM
        ARCHIVES
)
SELECT
	e.IDNUM as EmployeeID,
	'' as BadgeID,
	'' as EmployeeExternalID,
	'' as EINTaxID,
	'' as EINName,
	P.PunchDate as PayDate,
	'' as InDate,
    P.InTime,
    P.OutTime

FROM (
    SELECT
        PW.FILEKEY,
		FORMAT(CONVERT(DATE, PunchDateTime), 'MM/dd/yyyy') AS PunchDate,
        PW.PunchTime AS InTime,        
        LEAD(PW.PunchTime) OVER (PARTITION BY PW.filekey, PW.PunchDate ORDER BY PW.PunchDateTime) AS OutTime,
        ROW_NUMBER() OVER (PARTITION BY PW.filekey, PW.PunchDate ORDER BY PW.PunchDateTime) AS RowNum
    FROM
        PunchesWithRowNum PW
    JOIN
        ExpectedCounts EC ON PW.FILEKEY = EC.FILEKEY AND PW.PunchDate = EC.PunchDate AND pw.OWNERID = ec.UNIQUEID
    WHERE
        (PW.PunchDate = '11/1/2023' AND RowNum <= EC.TRANSCOUNT)
		OR
		(PW.PunchDate = '10/31/2023' AND RowNum = 1)

) AS P
Inner Join EMPLOYEES e
on p.FILEKEY = e.FILEKEY
WHERE
    P.RowNum % 2 = 1 -- Ensures we only get odd-numbered rows as in punches
order by CAST(e.IDNUM as int)

Content Inventory

Doc File: content/docs/reports/employee_information/UKGPUNCH.mdx
SQL Script: SQL/reports/employee_information/UKGPUNCH.sql
Image: public/img/screenshots/reports/exports/punchdata.png