WorkSync Schedules Report Version 3

This is the enhanced version 3 of the WorkSync schedule report that provides improved functionality and more sophisticated zone-based scheduling layout. It includes advanced features for better schedule organization and presentation.

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 with enhanced organization:

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)

Enhanced features include:

Dynamic zone counting and iteration
Improved zone-based organization
Better handling of multiple employees per zone/shift
Enhanced open shift tracking

Output Format

The report uses a sophisticated table structure with dynamic zone handling:

Zone	Day Employees	Day Positions	Day Times	Evening Employees	Evening Positions	Evening Times	Night Employees	Night Positions	Night Times
Zone Header
Zone Data	Employee Name	Position	Shift Time	Employee Name	Position	Shift Time	Employee Name	Position	Shift Time

Technical Implementation

The script uses advanced features:

Dynamic zone counting with @ZoneCount variable
WHILE loop processing for iterating through zones
Temporary table storage (@Temp) for complex data organization
ROW_NUMBER() functions for zone ordering and alignment
Complex FULL JOIN operations across all three shifts
Zone-specific filtering using @WESTBloom table variable
Enhanced time formatting with RIGHT and CONVERT functions

Zone Configuration

The V3 report uses a predefined set of zones:

Zone numbers: 2, 4, 5, 6, 7, 12, 29
These correspond to specific areas within the facility
Zone names are extracted using SUBSTRING(w5.NAME, 4, 30)

Enhanced Features

Dynamic Processing - Handles varying numbers of zones automatically
Improved Organization - Better alignment of data across shifts
Flexible Zone Selection - Easy modification of included zones
Enhanced Data Structure - More sophisticated output formatting
Better Performance - Optimized query structure for larger datasets

Use Cases

Advanced operational schedule management
Multi-zone facility coordination
Complex staffing pattern analysis
Enhanced schedule presentation for management
Foundation for further specialized variations

Improvements Over Base Version

Dynamic zone handling instead of static queries
Better data organization and presentation
More flexible zone configuration
Enhanced performance for complex scenarios
Improved maintainability and extensibility

T-SQL

worksync_schedules_report_v3.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),empZoneE Varchar (30), empnameE Varchar (60), positionE varchar(30), ShifttimeE varchar(20), empZoneN Varchar (30), empnameN Varchar (60), positionN varchar(30), ShifttimeN 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, ROW_NUMBER() 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.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 '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, 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.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 '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, 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)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 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)

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,Null,Null,Null,Null,Null,Null,Null,Null,@Row from Zones z where z.Rownum=@Row
	UNION
	Select 'zzz', d.Zone, d.empname, d.Position, d.shifttime,
	e.Zone, e.empname, e.Position, e.shifttime,
	null, null,	null, null,
	@Row 
	
	From Days d

	Full Join
	Evenings e 
	on e.Zone=d.Zone
	
	where d.Zone=(select z.NAME from Zones z where z.Rownum=@Row)
	
	
	Set @Row = @Row+1
End

Select * from @Temp order by 14,1



/*(Select e.empname From Evenings e where e.Zone=(select z.NAME from Zones z where z.Rownum=@Row)),
	(Select e.Position From Evenings e where e.Zone=(select z.NAME from Zones z where z.Rownum=@Row)),
	(Select e.shifttime From Evenings e where e.Zone=(select z.NAME from Zones z where z.Rownum=@Row)),
	(Select n.Zone From Nights n where n.Zone=(select z.NAME from Zones z where z.Rownum=@Row)),
	(Select n.empname From Nights n where n.Zone=(select z.NAME from Zones z where z.Rownum=@Row)),
	(Select n.Position From Nights n where n.Zone=(select z.NAME from Zones z where z.Rownum=@Row)),
	(Select n.shifttime From Nights n where n.Zone=(select z.NAME from Zones z where z.Rownum=@Row))*/

Content Inventory

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

WorkSync Schedules Report Version 3

On this page