Riverview WorkSync Schedule Report

This is a specialized version of the WorkSync schedule report customized for the Riverview location. It focuses on day shifts only and uses Work Group 4 zones instead of Work Group 5, with specific position filtering for the Riverview facility.

Parameters

@MIN - Start date for the report (format: {mindate})
@MAX - End date for the report (format: {maxdate})
@LOC - Location identifier (set to 7 for Riverview)

Data Components

The report consists of 1 main data section:

Day Shifts Only - Covers shifts from 06:00:00 to 13:59:00

Features include:

Scheduled employees for day shifts at Riverview
Open day shifts from unfulfilled schedules
Work Group 4 zone-based organization (instead of WG5)
Specific position filtering for Riverview operations

Output Format

Simplified single-shift format focusing on Riverview day operations:

Zone	Employee Name	Position	Shift Time
Zone Name	Employee Name	Position Title	Start - End Time
	OPEN SHIFT	Position Title	Start - End Time

Technical Implementation

The script uses:

Work Group 4 zones instead of Work Group 5
Location-specific filtering (WG1 = 7 for Riverview)
Position filtering using predefined position numbers
Zone name formatting with special handling for zone 1
Day shift time filtering (06:00:00 to 13:59:00)
Dynamic zone processing with WHILE loop iteration

Zone Configuration

Riverview-specific zones:

Zone numbers: 1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21
Special formatting for zone 1: SUBSTRING(w4.NAME,2,30)
Other zones: SUBSTRING(w4.NAME,8,30)

Position Configuration

Riverview-specific positions:

Position numbers: 14, 44, 45, 46, 47, 51, 59, 71, 82, 83, 84, 85, 86
These correspond to specific roles at the Riverview facility

Use Cases

Riverview Daily Operations - Location-specific schedule management
Day Shift Planning - Focus on morning operations at Riverview
Position-Specific Staffing - Track specific roles at the facility
Open Shift Management - Identify unfilled positions at Riverview
Location Compliance - Ensure adequate staffing for Riverview requirements

Differences from Standard WorkSync

Work Group 4 zones instead of Work Group 5
Location-specific filtering (WG1 = 7)
Day shifts only (no evening or night shifts)
Position filtering for Riverview-specific roles
Custom zone name formatting for Riverview zones

T-SQL

riverview_version_of_worksync_sechedule_report.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE, @ZoneCount int, @Row int, @LOC int
DECLARE @Temp Table (Zone varchar(30),empname Varchar (60), position varchar(30), Shifttime varchar(20),RowOrder Int)
DECLARE	@LZones Table(Num smallint)
DECLARE @POSITIONS Table(Num Smallint)
insert into @LZones(Num) Values (1),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21)
insert into @POSITIONS(Num) Values (14),(44),(45),(46),(47),(51),(59),(71),(82),(83),(84),(85),(86)

Set @LOC = 7
SET @MIN={mindate}
SET @MAX={maxdate}

Set @Row = 1
;

Select @ZoneCount = COUNT(*) from WORKGROUP4 w4 where w4.WGNUM in (Select num from @LZones)

Insert Into @Temp Select Null,'DAY',NULL,NULL,0

While @Row<=@ZoneCount
Begin
	
WITH Zones as (
	Select w4.WGNUM, Case
		When w4.WGNUM = 1 then SUBSTRING(w4.NAME,2,30)
		Else SubString(w4.NAME,8,30)
		End as NAME,
		ROW_NUMBER() Over (Order by Case 
									When w4.WGNUM = 1 then SUBSTRING(w4.NAME,2,30)
									Else SubString(w4.NAME,8,30)
									End) as Rownum 
	from WORKGROUP4 w4 where w4.WGNUM in (Select num from @LZones)
),

Days as (
select t.wg4num,Case when t.wg4num=1 then SUBSTRING(t.wg4,2,30) else Substring(t.wg4,8,30) end as Zone, t.wg3 as Position,t.schdate as schdate,t.shifttime as shifttime,t.empname as empname,row_number() over (order by t.wg4, t.wg3) as numero
from 
(
select distinct wg4.WGNUM as wg4num, wg4.NAME as wg4, wg3.NAME as wg3,
sch.schdate as schdate,
Right(convert(varchar(20),sch.starttime,100),7)+' - '+ Right(convert(varchar(20),sch.endtime,100),7) as shifttime,
e.firstname+' '+e.lastname as empname
from schedules sch
inner join employees e
on e.filekey = sch.filekey
inner join WORKGROUP4 wg4
on wg4.WGNUM = sch.WG4
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 = @LOC
and sch.wg3 in (select num from @POSITIONS)
and sch.WG4 in (select num from @LZones)
and sch.schtype in (0)

union all

select distinct wg4.WGNUM as wg4num, wg4.NAME as wg4, wg3.NAME as wg3,
sch.schdate as schdate,
Right(convert(varchar(20),sch.mindate,100),7)+' - '+ Right(convert(varchar(20),sch.maxdate,100),7) as shifttime,
'OPEN SHIFT' as empname
from unfulfilledschs sch
inner join WORKGROUP4 wg4
on wg4.WGNUM = sch.WG4
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 = @LOC
and sch.wg3 in (select num from @POSITIONS)
and sch.WG4 in (select num from @LZones)
and sch.schtype in (0)

)t
)

	Insert Into @Temp

	Select z.NAME,Null,Null,Null,@Row from Zones z where z.Rownum=@Row
	UNION
	Select 'zzz',d.empname,d.Position,d.shifttime,@Row 
	From Days d
	
	where d.wg4num = (Select z.WGNUM from Zones z where z.Rownum=@Row)
	
	Set @Row = @Row+1
End

Select * from @Temp order by 5,1,3

Content Inventory

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

Riverview WorkSync Schedule Report

On this page