Comp Payout Report

Report that identifies employees with high compensatory time balances that may be eligible for payout within a specified date range, showing current benefit amount and compensatory time usage

Parameters

{mindate} - Start date for the query (example: '2023-01-01')
{maxdate} - End date for the query (example: '2023-12-31')

Data Components

The report consists of one main query with two subqueries that calculate:

Main Query - Joins employee data with their benefit balances
CompPayout Subquery - Calculates the total comp time paid out (paydesnum=32)
CompUsed Subquery - Calculates the total comp time used (paydesnum=13)

Output Format

The report outputs a table with employee information and their compensatory time details.

Column	Description
Name	Employee's full name (Last, First)
idnum	Employee's ID number
amount	Current compensatory time balance
CompPayout	Total comp time paid out during the period
CompUsed	Total comp time used during the period
REMAIN	Remaining comp time balance

Technical Implementation

The script uses:

Subqueries with aggregation (SUM) to calculate total hours for specific pay designation numbers
Filtering for employees with high comp time balances (greater than 80 hours)
Filtering by pay period beginning date
Excluding employees with placeholder ID numbers (999999999)
Joining employee data with their benefit records

T-SQL

comp_payout.sql

select
    e.lastname+', '+e.firstname,
    e.idnum,
    eb.amount,
    (Select Sum(Hours)
    From Paydeshist
    where filekey=e.filekey and paydesnum=32 and eventdate between {mindate} and {maxdate} ) as CompPayout,
    (Select Sum(Hours)
    From Paydeshist
    where filekey=e.filekey and paydesnum=13 and eventdate between {mindate} and {maxdate} ) as CompUsed,
    eb.REMAIN
from employees e, EMPLOYEEBENEFITS eb
where e.filekey=eb.filekey
    and eb.benefit=2
    and (eb.amount>80 or eb.remain>80)
    and eb.PAYPERBEGINNING Between {mindate} and {maxdate}
    and e.idnum !='999999999'

Content Inventory

Doc File: content/docs/reports/employee_information/comp_payout.mdx
SQL Script: SQL/reports/employee_information/comp_payout.sql