PTO No Incident Export

This script generates an export file for time-off transactions that are NOT associated with attendance incidents. It's the counterpart to the PTO incident export, specifically designed to export clean PTO (Personal Time Off) usage where employees had no attendance issues on the same day.

Parameters

• @MIN - Start date for the export period (example: '10/31/2021')
• @MAX - End date for the export period (example: '11/6/2021')

Data Components

The export consists of 2 main data elements:

1. Clean Time Off Transaction Data - PTO usage details for employees with no incidents
2. Incident Exclusion Logic - Ensures only time off without associated incidents is exported

Output Format

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

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 Exclusion Logic - Filters out records where incidents exist using RULESOURCE values 39-48
• Conditional Filtering - Only includes records where incident count equals zero
• Data Aggregation - Groups and sums hours by employee and date
• Employee Field Mapping - Uses employee field 16 for time off policy information

Key Differences from PTO Incident Export

• Employee Field: Uses field 16 instead of field 15 for time off policy
• Incident Logic: Excludes records with incidents (count = 0) instead of including them
• No Comment Field: Does not include incident labels since no incidents exist
• Clean Data Focus: Specifically targets "perfect" time off usage

Notes

• This export complements the PTO incident export by capturing the opposite scenario
• Only exports PTO usage (pay designation 6) that has NO correlation with incidents
• RULESOURCE values 39-48 represent specific types of attendance incidents that are excluded
• Employee field 16 contains the time off policy name (different from incident export)
• The export format is designed for integration with external HR/payroll systems
• Results are grouped to prevent duplicate entries for the same employee/date combination
• Useful for identifying employees who use time off responsibly without attendance issues

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @MIN DATE, @MAX DATE

/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='10/31/2021'
SET @MAX='11/6/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

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

Where  
 e.FILEKEY=cf.FILEKEY 
and cf.FIELDID=16
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

Content Inventory

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