PTO True-Up Report

This report calculates PTO (Paid Time Off) true-up calculations for active employees within a specified date range. It aggregates regular, overtime, and double-time hours to calculate ESTA (Employee Sick Time Accrual) values and provides a framework for PTO accrual rate calculations.

Parameters

@StartDate - Start date for the calculation period (example: '2021-03-07')
@EndDate - End date for the calculation period (example: '2021-03-14')

Data Components

The report consists of 4 main data aggregation sections:

Regular Hours - Standard work hours (paydesnum = 1)
Overtime Hours - Overtime work hours (paydesnum = 2)
Double Time Hours - Double-time work hours (paydesnum = 3)
Employee Information - Demographics and work location details

Output Format

The report returns employee PTO calculation data with the following structure:

Column	Description
idnum	Employee ID number
Name	Employee name (Last, First format)
Location	Work location (Workgroup 4)
Department	Department name (Workgroup 1)
Shift	Shift assignment (Workgroup 2)
PAWeeks	Perfect attendance weeks calculation
RegHours	Regular hours worked
OTHours	Overtime hours worked
DTHours	Double-time hours worked
TotalHours	Sum of all hours worked
ESTACalc	ESTA calculation (Total Hours ÷ 30)
RateOfAccrual	Placeholder for accrual rate
RateXWeeks	Placeholder for rate × weeks calculation
PTOTrueUp	Placeholder for final PTO true-up amount

Technical Implementation

The script uses:

Common Table Expressions (CTEs) for data filtering and aggregation
LEFT JOINs to ensure all active employees are included
CASE statements for perfect attendance weeks parsing
String manipulation functions (CHARINDEX, SUBSTRING, LTRIM)
Conditional aggregation with ISNULL and ROUND functions
Employee field ID 12 for perfect attendance tracking

Notes

Excludes salary employees (payclass 8, 16)
Excludes temporary employees (status 2-13)
Only includes active employees (activestatus = 0)
Perfect attendance weeks extracted from employee field 12
ESTA calculation uses 30-hour divisor
Placeholder columns provided for future rate calculations

T-SQL

TrueUp.sql

--Example clients MITurkey
DECLARE @StartDate DATE = '2021-03-07';  
DECLARE @EndDate DATE = '2021-03-14';    

WITH FilteredPay AS (
    SELECT filekey, hours, PAYDESNUM
    FROM PAYDESHIST
    WHERE eventdate BETWEEN @StartDate AND @EndDate
),
RegHours AS (
    SELECT filekey, SUM(hours) AS RegHours 
    FROM FilteredPay 
    WHERE PAYDESNUM = 1 
    GROUP BY filekey
),
OTHours AS (
    SELECT filekey, SUM(hours) AS OTHours 
    FROM FilteredPay 
    WHERE PAYDESNUM = 2 
    GROUP BY filekey
),
DTHours AS (
    SELECT filekey, SUM(hours) AS DTHours 
    FROM FilteredPay 
    WHERE PAYDESNUM = 3 
    GROUP BY filekey
)
SELECT 
    e.idnum,
    e.lastname + ', ' + e.firstname AS Name,
    w4.name AS Location,
    w1.name AS Department,
    w2.name AS Shift,
    CASE 
        WHEN ef.ASSTRING IS NULL OR ef.ASSTRING = 'NA' 
            THEN ef.ASSTRING
        ELSE 
            CASE 
                WHEN CHARINDEX('of', ef.ASSTRING) > 0 AND CHARINDEX('weeks possible', ef.ASSTRING) > 0 
                THEN LTRIM(SUBSTRING(ef.ASSTRING, CHARINDEX('of', ef.ASSTRING) + 3, 
                                     CHARINDEX('weeks possible', ef.ASSTRING) - CHARINDEX('of', ef.ASSTRING) - 3))
                ELSE ef.ASSTRING
            END
    END AS PAWeeks,
    ISNULL(Round(RegHours.RegHours, 2), 0) AS RegHours,
    ISNULL(Round(OTHours.OTHours, 2), 0) AS OTHours,
    ISNULL(Round(DTHours.DTHours, 2), 0) AS DTHours,
    ROUND(ISNULL(RegHours.RegHours, 0) + ISNULL(OTHours.OTHours, 0) + ISNULL(DTHours.DTHours, 0), 2) AS TotalHours,
    ROUND((ISNULL(RegHours.RegHours, 0) + ISNULL(OTHours.OTHours, 0) + ISNULL(DTHours.DTHours, 0)) / 30, 2) AS ESTACalc,
    '' AS RateOfAccrual,
    '' AS RateXWeeks,
    '' AS PTOTrueUp
FROM 
    Employees e
    LEFT JOIN Workgroup4 w4 ON e.WG4 = w4.WGNUM
    LEFT JOIN Workgroup1 w1 ON e.WG1 = w1.WGNUM
    LEFT JOIN Workgroup2 w2 ON e.WG2 = w2.WGNUM
    LEFT JOIN EMPLOYEEFIELDS ef ON e.filekey = ef.filekey
    LEFT JOIN RegHours ON e.filekey = RegHours.filekey
    LEFT JOIN OTHours ON e.filekey = OTHours.filekey
    LEFT JOIN DTHours ON e.filekey = DTHours.filekey
WHERE 
    ef.FIELDID = 12
    AND e.ACTIVESTATUS = 0
    AND e.Payclass NOT IN (8,16) --Exclude Salary
    AND e.STATUS NOT IN (2,3,4,5,6,7,8,9,10,11,12,13) --Exclude Temps
ORDER BY 
    Name;

Content Inventory

Doc File: content/docs/reports/pay_information/trueup.mdx
SQL Script: SQL/reports/pay_information/TrueUp.sql