Regular vs Benefit Schedule by Week Report

This report provides a comparative analysis of regular schedules (SCHTYPE=0) versus benefit schedules (SCHTYPE=2) broken down by day of the week over a 6-week period. It displays scheduled hours for active employees in workgroup 4, allowing for easy comparison between regular work time and benefit time usage patterns.

Parameters

The report uses dynamic date calculation:

Start Date - Automatically calculated as 6 weeks prior to current date (Sunday of that week)
End Date - Current week's Saturday
Date Range - 6 consecutive weeks from start to end date

Data Components

The report consists of 2 main data sections per week:

Regular Schedules (SCHTYPE=0) - Standard work schedules and hours
Benefit Schedules (SCHTYPE=2) - Benefit time, vacation, PTO, etc.

Example Screenshots

Regular vs Benefit Schedule by Week Report Example Example of the Regular vs Benefit Schedule by Week Report showing typical output format

Technical Implementation

The script uses:

Dynamic date calculation using DATEDIFF and DATEADD functions
While loop to iterate through 6 weeks of data
Subqueries for each day of the week to calculate daily totals
Table variable (@Temp) to store intermediate results
Hour conversion from minutes to decimal hours (hours/60.0)
ISNULL function to handle days with no scheduled hours
Employee filtering for workgroup 4 (WG4=1) and active status (ACTIVESTATUS=0)

Schedule Types Included

SCHTYPE=0 - Regular work schedules
SCHTYPE=2 - Benefit schedules (vacation, PTO, sick time, etc.)

Employee Filtering

Workgroup 4 only (WG4=1)
Active employees only (ACTIVESTATUS=0)

Use Cases

Analyzing work vs benefit time patterns across weeks
Identifying trends in vacation and PTO usage
Capacity planning for regular vs benefit time coverage
Budget analysis comparing productive hours to benefit hours
Workforce planning and scheduling optimization
Compliance monitoring for benefit time allocation
Historical analysis of work patterns over 6-week periods

T-SQL

reg_vs_ben_sch_by_week.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @CurrentDate Date
DECLARE @Min DATE
DECLARE @Max DATE
DECLARE @Sunday DATE
DECLARE @Monday DATE
DECLARE @Tuesday DATE
DECLARE @Wednesday DATE
DECLARE @Thursday DATE
DECLARE @Friday DATE
DECLARE @Saturday DATE

SET @CurrentDate = GETDATE()
SET @Min= DATEADD(DAY, -1, DATEADD(week, DATEDIFF(week, 0, @CurrentDate) -5, 0))
SET @Max= DATEADD(dd, 7-(DATEPART(dw, @CurrentDate)), @CurrentDate)

SET @Sunday=DATEADD(Day,0,@Min)
SET @Monday=DATEADD(DAY,1,@Min)
SET @Tuesday=DATEADD(DAY,2,@Min)
SET @Wednesday=DATEADD(DAY,3,@Min)
SET @Thursday=DATEADD(DAY,4,@Min)
SET @Friday=DATEADD(Day,5,@Min)
SET @Saturday=DATEADD(Day,6,@Min)

DECLARE @Temp Table (Week datetime,
    SCHTYPE int,
    Sunday decimal (38,2),
    Monday decimal (38,2),
    Tuesday decimal (38,2),
    Wednesday decimal (38,2),
    Thursday decimal (38,2),
    Friday decimal (38, 2),
    Saturday decimal (38,2),
    Totals decimal (38,2) )

While @CurrentDate<@Max
Begin

    Insert INTO @Temp

    Select @min, s.SCHTYPE,
        (Select isnull(Sum(sSun.hours)/60.0,0)
        from SCHEDULES sSun, EMPLOYEES eSun
        where sSun.FILEKEY=eSun.FILEKEY and sSun.SCHDATE=@Sunday and sSun.SCHTYPE=0 and eSun.WG4=1 and eSun.ACTIVESTATUS=0),
        (Select isnull(Sum(sMon.hours)/60.0,0)
        from SCHEDULES sMon, EMPLOYEES eMon
        where sMon.FILEKEY=eMon.FILEKEY and sMon.SCHDATE=@Monday and sMon.SCHTYPE=0 and eMon.wg4=1 and eMon.ACTIVESTATUS=0),
        (Select isnull(Sum(sTue.hours)/60.0,0)
        from SCHEDULES sTue, EMPLOYEES eTue
        where sTue.FILEKEY=eTue.FILEKEY and sTue.SCHDATE=@Tuesday and sTue.SCHTYPE=0 and eTue.wg4=1 and eTue.ACTIVESTATUS=0),
        (Select isnull(Sum(sWed.hours)/60.0,0)
        from SCHEDULES sWed, EMPLOYEES eWed
        where sWed.FILEKEY=eWed.FILEKEY and sWed.SCHDATE=@Wednesday and sWed.SCHTYPE=0 and eWed.wg4=1 and eWed.ACTIVESTATUS=0),
        (Select isnull(Sum(sTHU.hours)/60.0,0)
        from SCHEDULES sTHU, EMPLOYEES eThu
        where sThu.FILEKEY=eThu.FILEKEY and sTHU.SCHDATE=@Thursday and sTHU.SCHTYPE=0 and eTHU.WG4=1 and eThu.ACTIVESTATUS=0),
        (Select isnull(Sum(sFri.hours)/60.0,0)
        from SCHEDULES sFri, EMPLOYEES eFri
        where sFri.FILEKEY=eFri.FILEKEY and sFri.SCHDATE=@Friday and sFri.SCHTYPE=0 and eFri.WG4=1 and eFri.ACTIVESTATUS=0),
        (Select isnull(Sum(sSat.hours)/60.0,0)
        from SCHEDULES sSat, EMPLOYEES eSat
        where sSat.FILEKEY=eSat.FILEKEY and sSat.SCHDATE=@Saturday and sSat.SCHTYPE=0 and eSat.wg4=1 and eSat.ACTIVESTATUS=0),
        (Select isnull(Sum(sTOT.hours)/60.0,0)
        from SCHEDULES sTOT, EMPLOYEES eTOT
        where sTOT.FILEKEY=eTOT.FILEKEY and sTOT.SCHDATE>=@Sunday and sTOT.SCHDATE<=@Saturday and sTOT.SCHTYPE=0 and eTOT.wg4=1 and eTOT.ACTIVESTATUS=0)
    From SCHEDULES s
    where s.SCHTYPE=0
    group by s.SCHTYPE

    Insert INTO @Temp

    Select @min, s.SCHTYPE,
        (Select isnull(Sum(sSun.hours)/60.0,0)
        from SCHEDULES sSun, EMPLOYEES eSun
        where sSun.FILEKEY=eSun.FILEKEY and sSun.SCHDATE=@Sunday and sSun.SCHTYPE=2 and eSun.WG4=1 and eSun.ACTIVESTATUS=0),
        (Select isnull(Sum(sMon.hours)/60.0,0)
        from SCHEDULES sMon, EMPLOYEES eMon
        where sMon.FILEKEY=eMon.FILEKEY and sMon.SCHDATE=@Monday and sMon.SCHTYPE=2 and eMon.wg4=1 and eMon.ACTIVESTATUS=0),
        (Select isnull(Sum(sTue.hours)/60.0,0)
        from SCHEDULES sTue, EMPLOYEES eTue
        where sTue.FILEKEY=eTue.FILEKEY and sTue.SCHDATE=@Tuesday and sTue.SCHTYPE=2 and eTue.wg4=1 and eTue.ACTIVESTATUS=0),
        (Select isnull(Sum(sWed.hours)/60.0,0)
        from SCHEDULES sWed, EMPLOYEES eWed
        where sWed.FILEKEY=eWed.FILEKEY and sWed.SCHDATE=@Wednesday and sWed.SCHTYPE=2 and eWed.wg4=1 and eWed.ACTIVESTATUS=0),
        (Select isnull(Sum(sTHU.hours)/60.0,0)
        from SCHEDULES sTHU, EMPLOYEES eThu
        where sThu.FILEKEY=eThu.FILEKEY and sTHU.SCHDATE=@Thursday and sTHU.SCHTYPE=2 and eTHU.WG4=1 and eThu.ACTIVESTATUS=0),
        (Select isnull(Sum(sFri.hours)/60.0,0)
        from SCHEDULES sFri, EMPLOYEES eFri
        where sFri.FILEKEY=eFri.FILEKEY and sFri.SCHDATE=@Friday and sFri.SCHTYPE=2 and eFri.WG4=1 and eFri.ACTIVESTATUS=0),
        (Select isnull(Sum(sSat.hours)/60.0,0)
        from SCHEDULES sSat, EMPLOYEES eSat
        where sSat.FILEKEY=eSat.FILEKEY and sSat.SCHDATE=@Saturday and sSat.SCHTYPE=2 and eSat.wg4=1 and eSat.ACTIVESTATUS=0),
        (Select isnull(Sum(sTOT.hours)/60.0,0)
        from SCHEDULES sTOT, EMPLOYEES eTOT
        where sTOT.FILEKEY=eTOT.FILEKEY and sTOT.SCHDATE>=@Sunday and sTOT.SCHDATE<=@Saturday and sTOT.SCHTYPE=2 and eTOT.wg4=1 and eTOT.ACTIVESTATUS=0)
    From SCHEDULES s
    where s.SCHTYPE=2
    group by s.SCHTYPE

    Insert into @Temp
    Select null, null, null, null, null, null, null, null, null, null

    set @CurrentDate = DATEADD(Day,7,@Min)
    set @Min = DATEADD(Day,7,@Min)
    SET @Sunday=DATEADD(Day,0,@Min)
    SET @Monday=DATEADD(DAY,1,@Min)
    SET @Tuesday=DATEADD(DAY,2,@Min)
    SET @Wednesday=DATEADD(DAY,3,@Min)
    SET @Thursday=DATEADD(DAY,4,@Min)
    SET @Friday=DATEADD(Day,5,@Min)
    SET @Saturday=DATEADD(Day,6,@Min)
end

Select +'WK of '+ Convert(varchar,t.Week,1) as Week,
    case t.SCHTYPE 
	when 0 then 'Reg' 
	when 2 then 'Ben' 
	else null 
end as Type,
    t.Sunday,
    t.Monday,
    t.Tuesday,
    t.Wednesday,
    t.Thursday,
    t.Friday,
    t.Saturday,
    t.Totals

from @Temp t

Content Inventory

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

Regular vs Benefit Schedule by Week Report

On this page