WorkSync Night Shifts Only Report

This specialized version of the WorkSync V3 report focuses exclusively on night shift schedules (19:00-05:59). It provides a detailed view of overnight staffing without the complexity of day and evening shift data, including special handling for shifts that span midnight.

Parameters

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

Example Screenshots

WorkSync Night Shifts Only Report Parameters

Data Components

The report consists of 1 focused data section:

Night Shifts Only - Covers shifts from 19:00:00 to 05:59:00 (spanning two days)

Features include:

Scheduled employees for night shifts
Open night shifts from unfulfilled schedules
Zone-based organization for overnight coverage
Position details for night shift staff
Special handling for shifts crossing midnight

Output Format

Simplified single-shift format focusing on overnight 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:

Dual time range filtering for shifts spanning midnight:
- Evening start: 19:00:00 to 23:59:00 (same day)
- Morning end: 00:00:00 to 05:59:00 (next day)
Date adjustment logic using DATEADD(dd,+1,@min) for morning portions
Simplified CTE structure focusing on night shifts only
Zone-based organization using Work Group 5
Standard WorkSync V3 zone filtering with @WESTBloom table

Zone Coverage

Same zone configuration as WorkSync V3
Zone numbers: 2, 4, 5, 6, 7, 12, 29
Focused on overnight shift staffing patterns

Special Considerations

Midnight Spanning Logic:

Shifts starting 19:00-23:59 are captured from the report date
Shifts starting 00:00-05:59 are captured from the day after the report date
This ensures complete coverage of overnight shifts

Use Cases

Overnight Operations Planning - Focus on night shift staffing
Night Shift Management - Simplified view for night supervisors
Overnight Coverage Analysis - Ensure adequate night staffing
Night Shift Open Position Tracking - Identify overnight staffing gaps
Security and Safety Planning - Ensure minimum overnight staffing levels

Benefits

Reduced Complexity - Easier to read and understand
Faster Processing - Less data to process and display
Focused Analysis - Concentrate on night shift patterns
Simplified Distribution - Easier to share with night shift staff
Quick Reference - Fast overview of overnight operations
Midnight Handling - Proper logic for shifts crossing midnight

Overnight Considerations

This report is particularly useful for:

Ensuring adequate overnight security coverage
Managing night shift handoffs
Planning for reduced overnight staffing
Identifying critical overnight positions
Compliance with minimum staffing requirements

T-SQL

worksync_schedules_report_v3_night_shifts_only.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(20),RowOrder 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)

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

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 @Temp

	Select z.NAME,Null,Null,Null,@Row from Zones z where z.Rownum=@Row
	UNION
	Select 'zzz',n.empname,n.Position,n.shifttime,@Row 
	From Nights n
	
	where n.wg5num = (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/worksync_night_shifts_only.mdx
SQL Script: SQL/reports/worksync/riverview_version_of_worksync_sechedule_report.sql
Screenshot: public/img/screenshots/reports/schedule/NightSchedulesWS.jpg

WorkSync Night Shifts Only Report

On this page