WorkSync Comprehensive All Shifts Report

This is the most comprehensive WorkSync report that processes all three shifts (day, evening, and night) using separate table storage for each shift type. It provides the most detailed and organized view of complete facility scheduling across all time periods.

Parameters

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

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

Data Components

The report consists of 3 separate data processing sections:

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

Enhanced features include:

Separate table variables for each shift type
Position filtering for specific roles
Enhanced zone processing with special formatting
Comprehensive shift ordering and organization

Output Format

The report uses three separate result sets, one for each shift:

Day Shifts Table:

Zone	Employee Name	Position	Shift Time	Row Order	Shift Order
Zone Name	Employee Name	Position Title	Start - End Time	Zone Number	1

Evening Shifts Table:

Zone	Employee Name	Position	Shift Time	Row Order	Shift Order
Zone Name	Employee Name	Position Title	Start - End Time	Zone Number	2

Night Shifts Table:

Zone	Employee Name	Position	Shift Time	Row Order	Shift Order
Zone Name	Employee Name	Position Title	Start - End Time	Zone Number	3

Technical Implementation

The script uses advanced features:

Separate table variables for each shift (@Day, @Evening, @Nights)
Position filtering using @POSITIONS table variable
Enhanced zone formatting with special handling for zone 1
Shift ordering with dedicated shift order column
Triple WHILE loop processing for each shift type
Comprehensive zone and position filtering

Zone Configuration

Enhanced zone configuration:

Zone numbers: 1, 2, 4, 5, 6, 7, 12, 29
Special formatting for zone 1: SUBSTRING(w5.NAME,2,30)
Other zones: SUBSTRING(w5.NAME,6,30)

Position Configuration

Specific position filtering:

Position numbers: 90, 107, 113, 139, 166, 167, 185
These correspond to specific roles requiring comprehensive tracking

Use Cases

Complete Facility Overview - Comprehensive view of all shifts
24-Hour Operations Planning - Full day scheduling analysis
Detailed Staffing Analysis - Position-specific tracking across all shifts
Management Reporting - Executive-level comprehensive scheduling reports
Compliance Monitoring - Ensure adequate coverage across all time periods

Benefits

Complete Coverage - All shifts processed separately for maximum detail
Enhanced Organization - Separate tables for better data management
Position Filtering - Focus on specific critical roles
Comprehensive Analysis - Most detailed WorkSync report available
Flexible Output - Separate result sets for each shift type

Performance Considerations

Resource Intensive - Processes all shifts with separate table storage
Longer Execution Time - Triple processing for comprehensive coverage
Memory Usage - Multiple table variables for data storage
Recommended for - Detailed analysis rather than quick operational checks

T-SQL

worksync_schedules_report_v3_day_evenings_night.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE, @ZoneCount int, @Row int
DECLARE @Day Table (Zone varchar(30),empname Varchar (60), position varchar(30), Shifttime varchar(20),RowOrder Int,Shiftorder int)
DECLARE @Evening Table (Zone varchar(30),empname Varchar (60), position varchar(30), Shifttime varchar(20),RowOrder Int, Shiftorder int)
DECLARE @Nights Table (Zone varchar(30),empname Varchar (60), position varchar(30), Shifttime varchar(20),RowOrder Int, Shiftorder int)
DECLARE	@WESTBloom Table(Num smallint)
DECLARE @POSITIONS Table(Num Smallint)
insert into @WESTBloom(Num) Values (1),(2),(4),(5),(6),(7),(12),(29)
insert into @POSITIONS(Num) Values (90),(107),(113),(139),(166),(167),(185)
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='11/17/2022'
SET @MAX='11/17/2022'

Set @Row = 1
;

Select @ZoneCount = COUNT(*) from WORKGROUP5 w5 where w5.WGNUM in (Select num from @WESTBloom)

--***********************DAY****************************
Insert Into @Day Select 'DAY','DAY','DAY','DAY',0,1

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

Days as (
select t.wg5num,Case when t.wg5num=1 then SUBSTRING(t.wg5,2,30) else Substring(t.wg5,6,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.wg5, t.wg3) as numero
from 
(
select distinct wg5.WGNUM as wg5num, wg5.NAME as wg5, 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 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.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

union all

select distinct wg5.WGNUM as wg5num, wg5.NAME as wg5, 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 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.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

)t
)

	Insert Into @Day

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

--***********************EVENINGS****************************
Set @Row = 1
Insert Into @Evening Select 'EVENINGS','EVENINGS','EVENINGS','EVENINGS',0,2

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

Evenings as (
select t.wg5num,Case when t.wg5num=1 then SUBSTRING(t.wg5,2,30) else Substring(t.wg5,6,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.wg5, t.wg3) as numero
from 
(
select distinct wg5.WGNUM as wg5num, wg5.NAME as wg5, 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 WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.starttime) between '14:00:00' and '18:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

union all

select distinct wg5.WGNUM as wg5num, wg5.NAME as wg5, 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 WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.mindate) between '14:00:00' and '18:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

)t
)

	Insert Into @Evening

	Select z.NAME,Null,Null,Null,@Row,2 from Zones z where z.Rownum=@Row
	UNION
	Select 'zzz',e.empname,e.Position,e.shifttime,@Row,2 
	From Evenings e
	
	where e.wg5num = (Select z.WGNUM from Zones z where z.Rownum=@Row)
	
	Set @Row = @Row+1
End

--***********************NIGHTS****************************
Set @Row = 1
Insert Into @Nights Select 'NIGHTS','NIGHTS','NIGHTS','NIGHTS',0,3

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

Nights as (
select t.wg5num,Case when t.wg5num=1 then SUBSTRING(t.wg5,2,30) else Substring(t.wg5,6,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.wg5, t.wg3) as numero
from 
(
select distinct wg5.WGNUM as wg5num, wg5.NAME as wg5, 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 WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.starttime) between '19:00:00' and '23:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)
OR
convert(time,sch.starttime) between '00:00:00' and '05:59:00'
and sch.schdate between dateadd(dd,+1,@min) and dateadd(dd,+1,@max)
and sch.wg1 = 9
and sch.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

union all

select distinct wg5.WGNUM as wg5num, wg5.NAME as wg5, 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 WORKGROUP5 wg5
on wg5.WGNUM = sch.WG5
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
where convert(time,sch.mindate) between '19:00:00' and '23:59:00'
and sch.schdate between @min and @max
and sch.wg1 = 9
and sch.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)
OR
convert(time,sch.mindate) between '00:00:00' and '05:59:00'
and sch.schdate between dateadd(dd,+1,@min) and dateadd(dd,+1,@max)
and sch.wg1 = 9
and sch.wg3 in (select num from @POSITIONS)
and sch.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

)t
)

	Insert Into @Nights

	Select z.NAME,Null,Null,Null,@Row,3 from Zones z where z.Rownum=@Row
	UNION
	Select 'zzz',n.empname,n.Position,n.shifttime,@Row,3 
	From Nights n
	
	where n.wg5num = (Select z.WGNUM from Zones z where z.Rownum=@Row)
	
	Set @Row = @Row+1
End

Select * from @Day
UNION
Select * from @Evening 
UNION
Select * from @Nights
order by 6,5,1,3

Content Inventory

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

WorkSync Comprehensive All Shifts Report

On this page