WorkSync Day Shifts Processing Module

This processing module extracts and formats day 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 morning shift processing logic with enhanced position filtering.

Parameters

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

Data Components

The module consists of 2 main processing sections:

1. Zone Configuration - Sets up zone information with special formatting
2. Day Shifts Processing - Covers shifts from 06:00:00 to 13:59:00

Features include:

• Scheduled employees for day shifts
• Open day shifts from unfulfilled schedules
• Zone-based organization using Work Group 5
• Position filtering for specific roles
• Enhanced zone name formatting

Output Format

Comprehensive day shift data extraction:

Technical Implementation

The script uses:

• Enhanced zone configuration with special handling for zone 1
• Position filtering using @POSITIONS table variable
• Zone filtering using @WESTBloom table variable
• UNION ALL operations to combine scheduled and open shifts
• ROW_NUMBER() function for proper ordering
• Conditional zone formatting based on zone number

Zone Configuration

Enhanced zone configuration:

• Zone numbers: 1, 2, 4, 5, 6, 7, 12, 29 (includes zone 1)
• 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 day shift tracking

Key Features

• Position Filtering - Focuses on specific critical positions
• Enhanced Zone Handling - Includes zone 1 with special formatting
• Comprehensive Output - Includes zone numbers and detailed information
• Morning Focus - Specifically targets morning operations
• Flexible Integration - Compatible with various WorkSync report structures

Use Cases

• Modular Day Shift Processing - Reusable component for day shift data
• Position-Specific Analysis - Focus on critical morning positions
• Integration Component - Building block for larger WorkSync reports
• Morning Operations Planning - Detailed day shift data extraction
• Custom Report Building - Foundation for custom day shift reports

Integration with Other Reports

This processing module is used by:

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

Benefits

• Position Filtering - Focus on specific critical roles
• Enhanced Zone Support - Includes zone 1 with proper formatting
• Reusable Logic - Consistent day shift processing across reports
• Detailed Output - Comprehensive data for analysis
• Modular Design - Can be integrated into various report structures

Comparison with Other Shift Modules

• More Complex than Evening - Includes position filtering and zone 1 handling
• Simpler than Night Shifts - No midnight spanning logic required
• Enhanced Features - Position filtering not present in other modules

Related Modules

• WorkSync Evening Shifts Processing
• WorkSync Night Shifts Processing
• WorkSync Zones Configuration

T-SQL

DECLARE @MIN DATE, @MAX DATE, @ZoneCount int, @Row int
SET @MIN='11/17/2022'
SET @MAX='11/17/2022'

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)
;

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
)

Select * from Days

Content Inventory

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