Supplement Pay Report

This report provides a weekly breakdown of employee hours worked, displaying daily hours in a pivot table format along with PTO usage and rate calculations. It's designed to show supplement pay eligibility and weekly hour patterns for payroll analysis.

Parameters

• @StartDate - Start date of the week (example: '2021-03-07')
• @EndDate - End date of the week (example: '2021-03-14')

Data Components

The report consists of 4 main data sections:

1. Daily Hours Pivot - Hours worked each day of the week (paydesnum 1,2,3,19,20,21)
2. PTO Hours - Paid time off hours (paydesnum 6)
3. Employee Information - Demographics and organizational details
4. Rate Calculations - Daily and total rate calculations

Output Format

The report returns weekly pay data with the following structure:

Technical Implementation

The script uses:

• PIVOT operation to transform daily hours into columns
• DATEPART(weekday) function for day-of-week identification
• Multiple LEFT JOINs to preserve all employee records
• Subqueries for PTO hour calculation
• ISNULL functions for handling missing data
• ROUND functions for decimal precision

Pay Designation Categories

Work Hours (included in daily totals):

• 1: Regular hours
• 2: Overtime hours
• 3: Double-time hours
• 19, 20, 21: Special pay categories

PTO Hours (separate calculation):

• 6: Paid time off

Notes

• Week structure: Sunday (1) through Saturday (7)
• Includes all employees with hours in the specified period
• PTO hours calculated separately from work hours
• Rate calculations assume 8-hour standard day
• Results sorted by employee last name, first name
• Useful for identifying supplement pay eligibility
• Provides comprehensive weekly hour analysis
• Supports payroll verification and supplement calculations

T-SQL

DECLARE @StartDate DATE = '2021-03-07';  -- Set your desired start date
DECLARE @EndDate DATE = '2021-03-14';    -- Set your desired end date

SELECT 
    e.idnum,
    e.LastName,
    e.FirstName,
    ST.NAME AS Status,
    W1.NAME AS Department,
    W2.NAME AS Shift,
    ISNULL([1], 0) AS Sunday,
    ISNULL([2], 0) AS Monday,
    ISNULL([3], 0) AS Tuesday,
    ISNULL([4], 0) AS Wednesday,
    ISNULL([5], 0) AS Thursday,
    ISNULL([6], 0) AS Friday,
    ISNULL([7], 0) AS Saturday,
    ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) + ISNULL([7], 0) AS TotalHours,
    e.rate,
    e.rate * 8 AS RatePerDay,
    ISNULL(PTO.PTOHours, 0) AS TotalPTO
FROM 
    Employees e
    INNER JOIN (
        SELECT 
            filekey,
            DATEPART(weekday, eventdate) AS DayOfWeek,
            ROUND(SUM(hours), 2) AS HoursWorked
        FROM PAYDESHIST PD
        WHERE eventdate BETWEEN @StartDate AND @EndDate
        AND PD.PAYDESNUM IN (1,2,3,19,20,21)
        GROUP BY filekey, DATEPART(weekday, eventdate)
    ) AS SourceTable
    PIVOT (
        SUM(HoursWorked)
        FOR DayOfWeek IN ([1],[2],[3],[4],[5],[6],[7])
    ) AS PivotTable
    ON e.filekey = PivotTable.filekey
    LEFT JOIN (
        SELECT 
            filekey,
            ROUND(SUM(hours), 2) AS PTOHours
        FROM PAYDESHIST
        WHERE eventdate BETWEEN @StartDate AND @EndDate
        AND PAYDESNUM = 6
        GROUP BY filekey
    ) AS PTO
    ON e.filekey = PTO.filekey
    LEFT JOIN STATUSTYPES ST
    ON e.STATUS = ST.NUM
    LEFT JOIN WORKGROUP1 W1
    ON e.WG1 = W1.WGNUM
    LEFT JOIN WORKGROUP2 W2
    ON e.WG2 = W2.WGNUM
ORDER BY 
    e.LastName, e.FirstName;

Content Inventory

• Doc File: content/docs/reports/pay_information/supplement_pay.mdx
• SQL Script: SQL/reports/pay_information/SupplementPay.sql