WorkSync Schedules Report (Base Version)

This is the foundational WorkSync schedule report that provides a basic layout of employee schedules organized by zones and shift times. It serves as the base template for all other WorkSync report variations.

Parameters

@MIN - Start date for the report (example: '9/1/2022')
@MAX - End date for the report (example: '9/1/2022')

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

Data Components

The report consists of 3 main shift sections:

Day Shifts - Covers shifts from 06:00:00 to 13:59:00
Evening Shifts - Covers shifts from 14:00:00 to 18:59:00
Night Shifts - Covers shifts from 19:00:00 to 05:59:00 (spanning two days)

Each section includes:

Scheduled employees with their assigned shifts
Open shifts from unfulfilled schedules
Zone-based organization (Work Group 5)
Position information (Work Group 3)

Output Format

The report displays schedules organized by zones with three shift columns:

Zone	Day Shift	Evening Shift	Night Shift
Zone Name	Employee Name	Employee Name	Employee Name
	Position	Position	Position
	Shift Time	Shift Time	Shift Time

Technical Implementation

The script uses:

Common Table Expressions (CTEs) for organizing data by shift type
Zone filtering using Work Group 5 (WG5) for specific areas
Time range filtering with CONVERT(TIME) for precise shift categorization
UNION ALL operations to combine scheduled and open shifts
ROW_NUMBER() window functions for proper alignment
Work group filtering (WG1=9 for specific department)

Work Groups Included

Work Group 1 = 9 (specific department)
Work Group 5 zones for area organization
Schedule type 0 (regular schedules)

Zone Coverage

The base report covers all zones within the specified Work Group 1 department, providing comprehensive coverage of the facility's scheduling needs.

Use Cases

Daily operational schedule overview
Zone-based staffing verification
Shift coverage analysis
Open shift identification
Template for creating specialized variations

T-SQL

worksync_schedules_report.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE, @ZoneCount int, @Row int
DECLARE @Temp Table (Zone varchar(30),empname Varchar (60), position varchar(30), Shifttime varchar(10),RowOrder Int)
DECLARE	@WESTBloom Table(Num smallint)
insert into @WESTBloom(Num) Values (2),(4),(5),(6),(7),(12),(29)
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='9/1/2022'
SET @MAX='9/1/2022'

Set @Row = 1
;
WITH Zones as (
Select w5.WGNUM, SubString(w5.NAME,4,30) as NAME, ROW_NUMBER() Over (Order by w5.wgnum) as Rownum from WORKGROUP5 w5 where w5.WGNUM in (Select num from @WESTBloom)
),
Positions as (
Select w3.WGNUM, w3.NAME from WORKGROUP3 w3
),
Days as (
select Substring(t.wg5,4,30) as Zone, t.wg3 as Position,t.schdate as schdate,t.shifttime as shifttime,t.empname as empname,row_number() over (order by t.wg5, t.wg3, t.schdate)as numero
from 
(
select distinct wg5.NAME as wg5, wg3.NAME as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.starttime,108)+' - '+ convert(varchar(5),sch.endtime,108) as shifttime,
e.firstname+' '+e.lastname as empname
from schedules sch
inner join employees e
on e.filekey = sch.filekey
inner join WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.starttime) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

union all

select distinct wg5.NAME as wg5, wg3.NAME as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname
from unfulfilledschs sch
inner join WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.mindate) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

union all

select distinct wg5.NAME as wg5, wg3.NAME as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname
from unfulfilledschs sch
inner join WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.mindate) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)
and sch.QTYREMAIN = 2

union all

select distinct wg5.NAME as wg5, wg3.NAME as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname
from unfulfilledschs sch
inner join WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.mindate) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)
and sch.QTYREMAIN = 3

)t
)

Select @ZoneCount = COUNT(*) from Zones
While @Row<@ZoneCount
Begin
	Insert Into @Temp

	Select 'Day',NUll,NULL,NULL,1
	UNION
	Select * from Days

End

Content Inventory

Doc File: content/docs/reports/worksync/worksync_schedules_report_base.mdx
SQL Script: SQL/reports/worksync/riverview_version_of_worksync_sechedule_report.sql

WorkSync Schedules Report (Base Version)

On this page