Punch Details Report

Report that provides detailed punch time information for active employees within a specified date range, retrieving up to 8 punches per day for each employee and formatting them for easy reading

Parameters

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

Data Components

The report consists of one main query that retrieves employee and punch information, with subqueries to extract each sequential punch time.

Example Output

Punch Details Report Example

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

The report displays detailed punch time information for each employee, showing up to 8 punches per day in an easy-to-read format.

Technical Implementation

The script uses:

SET NOCOUNT ON and SET ANSI_WARNINGS OFF to improve performance
Multiple correlated subqueries to extract sequential punch times
CONVERT and ISNULL functions to format time values consistently
LEFT function to trim time formats to HH:MM
Filtering for active employees only (activestatus=0)
Filtering for days with more than 2 transactions

T-SQL

punch_details.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SELECT E.FILEKEY, E.IDNUM, E.LASTNAME+', '+E.FIRSTNAME+' '+E.INITIAL, cast({mindate} as datetime), cast({maxdate} as datetime), A.EVENTDATE, A.TRANSCOUNT,
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 0), 108), ''), 5), --PUNCH1
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 1), 108), ''), 5), --PUNCH2
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 2), 108), ''), 5), --PUNCH3
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 3), 108), ''), 5), --PUNCH4
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 4), 108), ''), 5), --PUNCH5
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 5), 108), ''), 5), --PUNCH6
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 6), 108), ''), 5), --PUNCH7
    LEFT(ISNULL(CONVERT(VARCHAR, (SELECT DTTMSTAMP
    FROM ARCTRANSACTIONS T
    WHERE OWNERID = A.UNIQUEID AND (SELECT COUNT(*)
        FROM ARCTRANSACTIONS
        WHERE OWNERID = A.UNIQUEID AND DTTMSTAMP < T.DTTMSTAMP) = 7), 108), ''), 5)
--PUNCH8
FROM ARCHIVES A, EMPLOYEES E
WHERE A.FILEKEY = E.FILEKEY
    AND A.EVENTDATE BETWEEN {mindate} AND {maxdate}
    AND A.TRANSCOUNT > 2
    AND E.ACTIVESTATUS=0
ORDER BY 2, 6

Content Inventory

Doc File: content/docs/reports/employee_information/punch_details.mdx
SQL Script: SQL/reports/employee_information/punch_details.sql
Screenshot: public/img/screenshots/reports/employee_information/RawTimecard.png