WorkSync Alternative Version Report

This alternative implementation of the WorkSync V3 report uses a different approach to data organization and output formatting. It provides a unified view of all shifts with enhanced shift identification and simplified table structure.

Parameters

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

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

Data Components

The report consists of 3 main shift sections processed in a unified approach:

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

Alternative features include:

Unified table structure for all shifts
Shift type identification in output
Simplified zone processing
Enhanced sorting and organization

Output Format

Unified table with shift type identification:

Zone	Shift Type	Zone Detail	Employee Name	Position	Shift Time	Row Order
Zone Header	null	null	null	null	null	Zone Number
Day	Zone Name	Employee Name	Position Title	Start - End Time	Zone Number	null
Evenings	Zone Name	Employee Name	Position Title	Start - End Time	Zone Number	null
Night	Zone Name	Employee Name	Position Title	Start - End Time	Zone Number	null

Technical Implementation

The script uses alternative approaches:

Single table variable (@Temp) for all shift data
Shift type identification in the first column
DENSE_RANK() function for zone ordering instead of ROW_NUMBER()
Unified UNION operations combining all shifts per zone
Simplified zone name extraction using SUBSTRING(w5.NAME,4,30)
Enhanced sorting by row order, shift type, and zone

Zone Configuration

Standard WorkSync zone configuration:

Zone numbers: 2, 4, 5, 6, 7, 12, 29 (excludes zone 1)
Consistent formatting: SUBSTRING(w5.NAME,4,30)
DENSE_RANK ordering for proper zone sequence

Key Differences from Standard V3

Unified Output - All shifts in single result set with shift identification
DENSE_RANK Usage - Different ranking approach for zone ordering
Simplified Structure - Single table variable instead of complex multi-table approach
Enhanced Sorting - Multi-level sorting for better organization
Shift Labeling - Clear identification of shift types in output

Use Cases

Unified Shift Analysis - Single view of all shifts with clear identification
Alternative Data Format - Different output structure for specific reporting needs
Simplified Processing - Less complex than full V3 implementation
Custom Integration - Alternative format for systems requiring different data structure
Comparative Analysis - Side-by-side shift comparison within zones

Benefits

Simplified Structure - Easier to understand and maintain
Unified Output - Single result set for all shifts
Clear Shift Identification - Easy to distinguish between shift types
Flexible Sorting - Multiple sorting options for different views
Reduced Complexity - Simpler than comprehensive all-shifts version

Performance Characteristics

Moderate Resource Usage - More efficient than comprehensive version
Single Result Set - Faster processing than multiple table approach
Simplified Logic - Easier to debug and maintain
Good Balance - Between simplicity and functionality

T-SQL

worksync_schedules_report_v3_alt_version.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE, @ZoneCount int, @Row int
DECLARE @Temp Table (Zone varchar(30),empZoneD Varchar (30), empname Varchar (60), position varchar(30), Shifttime varchar(20),RowOrder Int)
DECLARE	@WESTBloom Table(Num smallint)
insert into @WESTBloom(Num) Values (2),(4),(5),(6),(7),(12),(29)
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='9/1/2022'
SET @MAX='9/1/2022'

Set @Row = 1
;

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

--Insert Into @Temp Select null,Null,'DAY',NULL,NULL,Null,'EVENINGS',Null,Null,Null,'NIGHTS',Null,Null,0

While @Row<=@ZoneCount
Begin
	
	WITH Zones as (
Select w5.WGNUM, SubString(w5.NAME,4,30) as NAME, DENSE_RANK() Over (Order by w5.NAME) as Rownum from WORKGROUP5 w5 where w5.WGNUM in (Select num from @WESTBloom)
),
Days as (
select Substring(t.wg5,4,30) as Zone,t.wg5num as ZoneNum, 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, t.schdate)as numero
from 
(
select distinct wg5.NAME as wg5, wg5.WGNUM as wg5num, 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.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

union all

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

)t
),

Evenings as (
select Substring(t.wg5,4,30) as Zone,t.wg5num as zonenum, 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, t.schdate)as numero
from 
(
select distinct wg5.NAME as wg5,wg5.WGNUM as wg5num, 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.WG5 in (select num from @WESTBloom)
and sch.schtype in (0)

union all

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

)t
),

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, t.schdate)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
)

	Insert Into @Temp

	Select z.NAME,Null,Null,Null,null,@Row from Zones z where z.Rownum=@Row
	UNION
	Select 'Day', d.Zone, d.empname, d.Position, d.shifttime,@Row 
		
	From Days d
	where d.Zone=(Select z.NAME from Zones z where z.Rownum=@Row)
	UNION
	Select 'Evenings', e.Zone, e.empname, e.Position, e.shifttime,@Row 
		
	From Evenings e
	where e.Zone=(Select z.NAME from Zones z where z.Rownum=@Row)
	UNION
	Select 'Night', n.Zone, n.empname, n.Position, n.shifttime,@Row 
		
	From Nights n
	where n.Zone=(Select z.NAME from Zones z where z.Rownum=@Row)
	
	
	Set @Row = @Row+1
End

Select * from @Temp order by 6,2,1

Content Inventory

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

WorkSync Alternative Version Report

On this page