PTO Incident Export

This script generates an export file for time-off transactions that are associated with attendance incidents. It's specifically designed to export PTO (Personal Time Off) data for employees who had incidents on the same day they used time off, formatted for external payroll or HR systems.

Parameters

@MIN - Start date for the export period (example: '10/25/2021')
@MAX - End date for the export period (example: '10/25/2021')

Data Components

The export consists of 2 main data elements:

Time Off Transaction Data - PTO usage details formatted for external systems
Incident Correlation - Links time off usage to attendance incidents that occurred on the same date

Output Format

The export returns time-off transaction data with the following structure:

Column	Description
PositionID	Employee position ID (prefixed with 'GPT')
TimeOffPolicyName	Time off policy name from employee field 15
TransactionType	Always 'Taken' for this export
ReasonCodes	Pay designation name for the time off
TransactionStartDate	Date the time off was taken
TransactionStartTime	Always '8:00 AM' for this export
TransactionAmount	Total hours of time off taken
TransactionUnit	Always 'hours' for this export
SendToPayroll	Always 'N' for this export
Comment	Incident label from the associated incident

Technical Implementation

The script uses:

Date Range Filtering - Parameterized date range for flexible export periods
Multi-table Joins - Combines employee, employee fields, pay designations, and pay history data
Incident Correlation - Links time off to incidents using RULESOURCE values 39-48
Conditional Filtering - Only includes records where incidents exist on the same date
Data Aggregation - Groups and sums hours by employee and date
Subquery for Comments - Retrieves incident labels for context

Notes

Example client mentioned: MITurkey
Only exports PTO usage (pay designation 6) that correlates with incidents
RULESOURCE values 39-48 represent specific types of attendance incidents
Employee field 15 contains the time off policy name
The export format appears designed for integration with external HR/payroll systems
Results are grouped to prevent duplicate entries for the same employee/date combination

T-SQL

pto_incident_export.sql

--Example clients MITurkey  
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @MIN DATE, @MAX DATE

/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='10/25/2021'
SET @MAX='10/25/2021'

Select 'GPT'+ E.IDNUM as PositionID, CF.AsString as TimeOffPolicyName, 'Taken' as TransactionType, PDS.NAME as ReasonCodes, PD.EVENTDATE as TransactionStartDate, '8:00 AM' as TransactionStartTime, SUM(PD.Hours) as TransactionAmount, 'hours' as TransactionUnit, 'N' as SendToPayroll
,(Select Top 1 INCIDENTLABEL from INCIDENTS where FILEKEY=e.FILEKEY and EVENTDATE=pd.EVENTDATE and RULESOURCE in (39,40,41,42,43,44,45,46,47,48)) as Comment

from Employees E, EMPLOYEEFIELDS CF, PAYDESIGNATIONS PDS, PAYDESHIST PD

Where  
 e.FILEKEY=cf.FILEKEY 
and cf.FIELDID=15
and pd.PAYDESNUM=PDS.PAYDESNUM
and E.FILEKEY=pd.FILEKEY
and pd.EVENTDATE Between @MIN and @MAX
and PD.PAYDESNUM=6
and (select COUNT(*) from INCIDENTS where FILEKEY=e.FILEKEY and EVENTDATE=pd.EVENTDATE and RULESOURCE in (39,40,41,42,43,44,45,46,47,48))>0

Group by E.IDNUM, CF.ASSTRING, PDS.NAME, PD.EVENTDATE, e.FILEKEY

Content Inventory

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