MITurkey Slimmed Down Perfect Attendance Report

This is a streamlined version of the perfect attendance report specifically customized for MITurkey. It focuses on specific pay designations (PST, POT, PDT) and provides a condensed view of employee time-off usage with organizational groupings.

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:

Employee Identification - Employee details including ID, name, and organizational structure
Organizational Groupings - Status types and workgroup classifications
Time Off Tracking - Specific pay designation usage (PST, POT, PDT) with hours and dollar amounts

Output Format

The report returns employee time-off data with the following structure:

Column	Description
filekey	Internal employee file key
idnum	Employee ID number
EmpName	Employee full name (Last, First Initial format)
NAME (Status)	Employee status type name
NAME (WG4)	Workgroup 4 name
NAME (WG1)	Workgroup 1 name
NAME (WG2)	Workgroup 2 name
ASSTRING	Employee field 12 value or 'N/A'
PeriodStart	Formatted start date of the period
Code	Pay designation code (PST, POT, or PDT)
Hours	Total hours for the pay designation
Dollars	Total dollar amount for the pay designation

Technical Implementation

The script uses:

Date Range Filtering - Parameterized date range for flexible reporting periods
Multiple Inner Joins - Ensures only employees with complete organizational data are included
Pay Designation Filtering - Specifically tracks pay designations 35 (PST), 36 (POT), and 37 (PDT)
Employee Status Filtering - Excludes pay classes 8 and 16, includes only active employees (status 0, activestatus 0)
Hire Date Validation - Only includes employees hired before the end of the reporting period
Data Aggregation - Groups and sums hours and dollars by employee and pay designation
Case Statement Logic - Converts pay designation numbers to readable codes

Notes

This is a client-specific version for MITurkey
Only includes active employees (status 0, activestatus 0)
Excludes certain pay classes (8, 16) that may represent contractors or special employee types
Employee field 12 provides additional categorization information
Results are ordered by employee file key for consistent output
The report focuses on personal time usage rather than incident tracking

T-SQL

miturkey_slimmed_down_pa_report.sql

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.filekey
,e.idnum
,e.lastname+', '+e.FIRSTNAME+' '+e.INITIAL as EmpName
,st.NAME
,w4.NAME
,w1.NAME
,w2.NAME
,ISNULL(cf.ASSTRING, 'N/A')
,Format(@MIN, 'MM/dd/yyyy') as PeriodStart
,Case
	When pd.paydesnum = 35 then 'PST'
	when pd.PAYDESNUM = 36 then 'POT'
	When pd.PAYDESNUM = 37 then 'PDT'
end as Code
,sum(pd.hours) as Hours
,sum(pd.dollars) as Dollars

from employees e
Inner Join PAYDESHIST pd
on e.FILEKEY=pd.FILEKEY
Inner Join STATUSTYPES st
on e.STATUS=st.NUM
Inner Join WORKGROUP4 w4
on e.WG4 = w4.WGNUM
Inner Join WORKGROUP1 w1
on e.WG1 = w1.WGNUM
Inner Join WORKGROUP2 w2
on e.WG2 = w2.WGNUM
Inner Join EMPLOYEEFIELDS cf
on e.FILEKEY = cf.FILEKEY

where
cf.FIELDID=12
and pd.EVENTDATE between '9/1/2021' and '9/30/2021'
and pd.PAYDESNUM in (35,36,37)
and e.PAYCLASS not in (8,16)
and e.STATUS = 0
and e.ACTIVESTATUS = 0
and e.HIRED<@MAX

Group by e.FILEKEY,e.idnum, e.LASTNAME,e.FIRSTNAME,e.INITIAL,st.NAME,w4.NAME,w1.NAME,w2.NAME,cf.ASSTRING, pd.PAYDESNUM
order by 1

Content Inventory

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