Worked vs Schedule Hours Difference Report

This report compares actual worked hours against scheduled hours for employees, highlighting discrepancies between what was scheduled and what was actually worked. It's useful for identifying overtime, undertime, and scheduling accuracy.

Parameters

No explicit parameters are defined in the script. The report uses all available data from the system.

Example Output

Worked vs Schedule Hours Difference Example

💡 Click the image above to zoom in and see the details more clearly!

The report displays a comparison between actual worked hours and scheduled hours, showing the difference for each employee shift. This helps identify discrepancies and potential overtime situations.

Technical Implementation

The script uses:

INNER JOIN between WORKGROUPTRANS and EMPLOYEES for employee details
LEFT JOIN with SCHEDULES to match worked time with scheduled time
Time formatting using CONVERT and LEFT functions for readable time display
Hour calculations converting minutes to decimal hours (HOURS / 60.00)
Pay class filtering restricting to pay classes 1 and 2
Date matching between worked events and scheduled dates

Pay Classes Included

Pay Class 1 and 2 employees only
Filters out other employee classifications

Use Cases

Identifying employees who consistently work over/under scheduled hours
Analyzing scheduling accuracy
Detecting potential overtime issues
Reviewing time and attendance compliance

T-SQL

worked_vs_schedule_diff.sql

select
    wt.FILEKEY,
    (e.lastname+ ', ' + e.firstname) as EmployeeName,
    e.idnum,
    convert(date,wt.EVENTDATE,101) as ShiftDate,
    DATENAME(dw,wt.EVENTDATE) as DOW,
    left(convert(varchar, wt.STARTTIME, 108), (5)) AS 'StartTime',
    --     FORMAT(wt.STARTTIME,'HH:MM') AS 'StartTime' ,  --      wt.STARTTIME,
    left(convert(varchar, wt.EndTIME, 108), (5)) AS 'EndTime',
    --     FORMAT(wt.EndTIME,'HH:MM') AS 'EndTime' , --    wt.ENDTIME,
    (wt.HOURS / 60.00) as wHours,
    (left(convert(varchar, wt.STARTTIME, 108), (5)) +'/'+ (left(convert(varchar, wt.EndTIME, 108), (5)) )) as Schedule,
    --     (FORMAT(s.STARTTIME,'HH:MM') +'/'+ FORMAT(s.EndTIME,'HH:MM') ) as Schedule,
    (s.HOURS / 60.00) as SchHrs,
    ((wt.HOURS / 60.00) - (s.HOURS / 60.00)) as Dif
-- p.NAME as PayClassName
-- p.PAYCLASSNUM
From
    WORKGROUPTRANS wt
    inner join Employees e
    on wt.FILEKEY=e.FILEKEY
    left join SCHEDULES s
    on wt.FILEKEY=s.FILEKEY and wt.EVENTDATE=s.SCHDATE
    inner join payclasses p
    on e.payclass = p.payclassnum
where 
       p.PAYCLASSNUM in (1,2)
--     and ((wt.HOURS / 60.00) - (s.HOURS / 60.00)) > 0
order by     
       EmployeeName,        ShiftDate

Content Inventory

Doc File: content/docs/reports/schedule/worked_vs_schedule_diff.mdx
SQL Script: SQL/reports/schedule/worked_vs_schedule_diff.sql
Screenshot: public/img/screenshots/reports/employee_information/ActvsSch.png