Perfect Attendance Report

This report generates a comprehensive view of employee attendance data including personal time off usage, missed punches, and incident tracking for a specified date range. It's designed to help managers evaluate employee attendance patterns and perfect attendance eligibility.

Parameters

• @MIN - Start date for the reporting period (example: '9/1/2021')
• @MAX - End date for the reporting period (example: '9/30/2021')

Data Components

The report consists of 3 main sections:

1. Employee Information - Basic employee details including ID, name, workgroup, and hire date
2. Time Off Usage - Personal time off hours used during the specified period
3. Attendance Issues - Missed punches and incident tracking with points and running totals

Output Format

The report returns employee attendance data with the following structure:

Technical Implementation

The script uses:

• Date Range Filtering - Parameterized date range for flexible reporting periods
• Multiple Table Joins - Combines employee, workgroup, and incident data
• Subqueries - Calculates PTO usage and missed punch counts
• Left Join Strategy - Includes employees even if they have no incidents
• Pay Designation Filtering - Specifically tracks pay designation 6 (Personal time off)
• Supervisor Edit History - Tracks missed punches through edit type 0, reason 0

Notes

• Example client mentioned: Westrock
• The script includes both active incident tracking and historical PTO usage
• Results are ordered by employee name and incident date for easy review
• Employees without incidents will still appear in results with NULL incident data

T-SQL

--Example clients Westrock
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE

SET @MIN={mindate}
SET @MAX={maxdate}
/*
SET @MIN='9/1/2021'
SET @MAX='9/30/2021'
*/
Select 
	e.IDNUM,
	e.LASTNAME+', '+e.FIRSTNAME as Fullname,
	w1.NAME,
	e.HIRED,
	(select IsNull(SUM(pd.hours),0) from PAYDESHIST pd where pd.FILEKEY = e.FILEKEY and pd.EVENTDATE between @MIN and @MAX and pd.PAYDESNUM = 6) as PerNoOTUsed,
	w4.NAME,
	(SELECT ISNULL(COUNT(*),0) FROM SUPEDITHIST WHERE FILEKEY=E.FILEKEY and EFFDATE between @MIN AND @MAX and EDITTYPE=0 and REASON=0) as MISSPUNCH,
	i.EVENTDATE,
	i.POINTS,
	i.RUNNINGPOINTS
From EMPLOYEES e
inner join WORKGROUP1 w1
on e.WG1 = w1.WGNUM
inner join WORKGROUP4 w4
on e.wg4 = w4.WGNUM
left join INCIDENTS i
on e.FILEKEY = i.FILEKEY

where i.EVENTDATE between @MIN and @MAX

order by Fullname, i.EVENTDATE

Content Inventory

• Doc File: content/docs/reports/perfect_attendance/pa_report.mdx
• SQL Script: SQL/reports/perfect_attendance/pa_report.sql