Schedule Hours by Day of Week Report

This report displays each active employee's scheduled hours broken down by day of the week for a specified week period. It provides a clear view of weekly scheduling patterns and total hours per employee.

Parameters

• @MIN - Start date of the week (Monday) (example: '2/15/2021')
• @MAX - End date of the week (Sunday) (example: '2/21/2021')

Note: The script includes parameter placeholders {mindate} and {maxdate} for integration with reporting systems.

Data Components

The report consists of 1 main data section:

1. Weekly Schedule Breakdown - Shows scheduled hours for each day of the week plus total

Output Format

Technical Implementation

The script uses:

• Subqueries for each day of the week to calculate daily hours
• DATEADD function to calculate each day from the start date
• ISNULL function to handle days with no scheduled hours (shows 0)
• Hour conversion from minutes to decimal hours (hours/60.0)
• Schedule type filtering (SCHTYPE=0 for regular schedules)
• Active employee filtering (ACTIVESTATUS = 0)

Schedule Types Included

• Schedule Type 0 (regular schedules only)
• Excludes benefit time, vacation, and other non-work schedule types

Use Cases

• Weekly schedule planning and review
• Identifying scheduling patterns and gaps
• Analyzing employee hour distribution across the week
• Payroll preparation and hour verification
• Compliance monitoring for full-time vs part-time status

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE
/*		
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='2/15/2021'
SET @MAX='2/21/2021'

Select e.lastname+', '+e.firstname, e.idnum,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE=@MIN) as Monday,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE=DATEADD(d,1,@MIN)) as Tuseday,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE=DATEADD(d,2,@MIN)) as Wednesday,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE=DATEADD(d,3,@MIN)) as Thursday,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE=DATEADD(d,4,@MIN)) as Friday,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE=DATEADD(d,5,@MIN)) as Saturday,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE=DATEADD(d,6,@MIN)) as Sunday,
    (Select ISNULL(Sum(hours)/60.0,0)
    from SCHEDULES
    where FILEKEY=e.FILEKEY and SCHTYPE=0 and SCHDATE between @MIN and @MAX) as Total
from EMPLOYEES e
where e.ACTIVESTATUS = 0
order by 1

Content Inventory

• Doc File: content/docs/reports/schedule/schedule_hours_by_day_of_week.mdx
• SQL Script: SQL/reports/schedule/reg_vs_ben_sch_by_week.sql