Punches In and Out Report

Report that provides a detailed view of employee punch in and punch out times within a specified date range, organizing data by employee and workgroup and handling both single and multiple punch pairs

Parameters

{mindate} - Start date for the query (example: '2023-01-01')
{maxdate} - End date for the query (example: '2023-12-31')

Data Components

The report consists of several Common Table Expressions (CTEs) that build upon each other:

PunchData - Retrieves all punches within the date range and assigns an order number to each punch per employee per day
FirstPunches - Extracts the first two punches (in/out) for each employee's day
SecondPunches - Extracts the second two punches (in/out) for employees who have 4 punches in a day

Output Format

The report outputs a combined list of all punch pairs, with employee and workgroup information.

Column	Description
CODE	Workgroup 1 code
IDNUM	Employee's ID number
EmployeeName	Formatted employee name (Last, First Middle)
PunchIn	Formatted date and time of punch in (MM/dd/yyyy HH:mm)
PunchOut	Formatted date and time of punch out (MM/dd/yyyy HH:mm)

Technical Implementation

The script uses:

Common Table Expressions (CTEs) for a modular approach
Window functions (ROW_NUMBER()) to sequence punches by employee and date
UNION ALL to combine first and second punch pairs
FORMAT() function to display dates in a consistent format
CASE statements to align punch in/out times
Joins with employee and workgroup tables to add identifying information

T-SQL

punches_in_and_out.sql

DECLARE @StartDate DATETIME = {mindate};
-- Replace with your desired start date
DECLARE @EndDate DATETIME = {maxdate};
-- Replace with your desired end date
WITH
    PunchData
    AS
    (
        SELECT
            T.FILEKEY,
            T.DTTMSTAMP,
            ROW_NUMBER() OVER (PARTITION BY T.FILEKEY, CAST(T.DTTMSTAMP AS DATE) ORDER BY T.DTTMSTAMP) AS PunchOrder,
            CAST(T.DTTMSTAMP AS DATE) AS PunchDate
        FROM
            TRANSACTIONS T
        WHERE 
        T.DTTMSTAMP BETWEEN @StartDate AND @EndDate
        -- Filter punches between the two dates
    ),
    FirstPunches
    AS
    (
        -- First punch in/out (first 2 punches for employees with 2 or 4 punches)
        SELECT
            P.FILEKEY,
            MIN(CASE WHEN P.PunchOrder = 1 THEN P.DTTMSTAMP END) AS PunchIn,
            MIN(CASE WHEN P.PunchOrder = 2 THEN P.DTTMSTAMP END) AS PunchOut
        FROM
            PunchData P
        WHERE 
        P.PunchOrder <= 2
        GROUP BY
        P.FILEKEY, P.PunchDate
    ),
    SecondPunches
    AS
    (
        -- Second punch in/out (3rd and 4th punches for employees with 4 punches)
        SELECT
            P.FILEKEY,
            MIN(CASE WHEN P.PunchOrder = 3 THEN P.DTTMSTAMP END) AS PunchIn,
            MIN(CASE WHEN P.PunchOrder = 4 THEN P.DTTMSTAMP END) AS PunchOut
        FROM
            PunchData P
        WHERE 
        P.PunchOrder > 2
        GROUP BY
        P.FILEKEY, P.PunchDate
    )
-- Combine FirstPunches and SecondPunches, and join with EMPLOYEES and WORKGROUP1 tables
    SELECT
        W1.CODE, -- Added W1.CODE before IDNUM
        E.IDNUM,
        E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + ISNULL(E.INITIAL, '') AS EmployeeName,
        FORMAT(FP.PunchIn, 'MM/dd/yyyy HH:mm') AS PunchIn,
        FORMAT(FP.PunchOut, 'MM/dd/yyyy HH:mm') AS PunchOut
    FROM
        FirstPunches FP
        JOIN
        EMPLOYEES E ON FP.FILEKEY = E.FILEKEY
        JOIN
        WORKGROUP1 W1 ON E.WG1 = W1.WGNUM
    -- Join with WORKGROUP1 to get W1.CODE
UNION ALL
    SELECT
        W1.CODE, -- Added W1.CODE before IDNUM
        E.IDNUM,
        E.LASTNAME + ', ' + E.FIRSTNAME + ' ' + ISNULL(E.INITIAL, '') AS EmployeeName,
        FORMAT(SP.PunchIn, 'MM/dd/yyyy HH:mm') AS PunchIn,
        FORMAT(SP.PunchOut, 'MM/dd/yyyy HH:mm') AS PunchOut
    FROM
        SecondPunches SP
        JOIN
        EMPLOYEES E ON SP.FILEKEY = E.FILEKEY
        JOIN
        WORKGROUP1 W1 ON E.WG1 = W1.WGNUM
-- Join with WORKGROUP1 to get W1.CODE
ORDER BY 
    W1.CODE, E.IDNUM, PunchIn;

Content Inventory

Doc File: content/docs/reports/employee_information/punches_in_and_out.mdx
SQL Script: SQL/reports/employee_information/punches_in_and_out.sql