WorkSync Night Shifts Processing Module

This processing module extracts and formats night shift data for use in WorkSync reports. It serves as a building block component that can be integrated into larger WorkSync report systems, focusing specifically on overnight shift processing logic.

Parameters

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

Data Components

The module consists of 1 focused processing section:

1. Night Shifts Processing - 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 using Work Group 5
• Special handling for shifts crossing midnight

Output Format

Simple night shift data extraction:

Technical Implementation

The script uses:

• Dual time range filtering for midnight-spanning shifts:
  • Evening start: 19:00:00 to 23:59:00 (same day)
  • Morning end: 00:00:00 to 05:59:00 (next day for open shifts)
• Zone filtering using @WESTBloom table variable
• UNION ALL operations to combine scheduled and open shifts
• ROW_NUMBER() function for proper ordering
• Time formatting using RIGHT and CONVERT functions

Zone Configuration

Standard WorkSync zone configuration:

• Zone numbers: 2, 4, 5, 6, 7, 12, 29
• Zone name formatting: SUBSTRING(wg5.NAME,4,30)

Midnight Spanning Logic

Special Considerations:

• Shifts starting 19:00-23:59 are captured from the report date
• Shifts starting 00:00-05:59 are captured from the same date for scheduled shifts
• Open shifts starting 00:00-05:59 use the next day date (DATEADD(dd,+1,@min))

Use Cases

• Modular Night Shift Processing - Reusable component for night shift data
• Integration Component - Building block for larger WorkSync reports
• Night Shift Analysis - Standalone night shift data extraction
• Development and Testing - Isolated testing of night shift logic
• Custom Report Building - Foundation for custom night shift reports

Integration with Other Reports

This processing module is used by:

• WorkSync V3 Full Report
• WorkSync Night Shifts Only
• WorkSync Comprehensive All Shifts
• WorkSync Alternative Version

Benefits

• Reusable Logic - Consistent night shift processing across reports
• Modular Design - Can be integrated into various report structures
• Midnight Handling - Proper logic for shifts crossing midnight
• Standardized Output - Consistent data format for integration
• Testing Isolation - Can be tested independently

Related Modules

• WorkSync Day Shifts Processing
• WorkSync Evening Shifts Processing
• WorkSync Zones Configuration

T-SQL

DECLARE @MIN DATE, @MAX DATE, @ZoneCount int, @Row int
SET @MIN='9/1/2022'
SET @MAX='9/1/2022'

DECLARE	@WESTBloom Table(Num smallint)
insert into @WESTBloom(Num) Values (2),(4),(5),(6),(7),(12),(29)
;

With Nights 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)as numero
from 
(
select distinct 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.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 @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,
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.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.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)
)t
)

Select * from Nights

Content Inventory

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