Daily Posting Report

This report provides a daily summary of scheduled staff, scheduled hours, and actual worked hours by position for a specific date. It helps managers compare planned staffing levels against actual staffing and hours worked.

Parameters

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

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

Data Components

The report consists of 4 main data sections:

1. Position - List of all work group 3 positions
2. Sch_staff - Scheduled staff count and hours by position and shift time
3. Whours - Actual worked hours by position from payroll data
4. Census - Coverage budget information (referenced but not fully implemented)

Output Format

The report outputs data in two sections:

Section 1 - Position Headers:

Section 2 - Shift Details:

Technical Implementation

The script uses:

• Common Table Expressions (CTEs) for organizing different data components
• GROUP BY clauses to aggregate data by position, date, and shift time
• Time formatting using RIGHT and CONVERT functions for readable shift times
• Hour calculations converting minutes to hours (HOURS/60)
• UNION operations to combine position headers with shift data
• Work group filtering (WG1=1) for specific department focus

Work Groups Included

• Work Group 1 = 1 (specific department)
• All Work Group 3 positions within that department
• Schedule type filtering applied

Use Cases

• Daily staffing level monitoring
• Comparing planned vs actual staffing
• Identifying scheduling gaps or overstaffing
• Daily operational reporting for management
• Tracking adherence to staffing plans

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='3/1/2023'
SET @MAX='3/1/2023'
;
With Position as (
	Select
		w3.NAME,
		w3.WGNUM
	from WORKGROUP3 w3
),

Sch_staff as (
Select 
	COUNT(s.filekey) as Staff,
	s.SCHDATE,
	Sum(s.HOURS/60) SchHours,
	Right(convert(varchar(20),s.starttime,100),7)+' - '+ Right(convert(varchar(20),s.endtime,100),7) as shifttime,
	s.WG3,
	s.WG1

From SCHEDULES s

Where
	s.SCHDATE between @MIN and @MAX
	and s.WG1=1

Group by s.SCHDATE, s.STARTTIME, s.ENDTIME, s.WG3, s.WG1
),

Whours as (
Select 
	pd.EVENTDATE,
	Round(Sum(pd.HOURS),2) wHours,
	pd.WG3,
	pd.WG1

From PAYDESHIST pd

Where
	pd.EVENTDATE between @MIN and @MAX
	and pd.WG1=1

Group by pd.EVENTDATE, pd.WG3, pd.WG1
),

Census as (
Select  
	cb.WG1,
	cb.ITEMDATA1,
	cb.COVERAGETYPE,
	cb.EVENTDATE
from COVBUDGETS cb where cb.COVERAGETYPE=2 and cb.EVENTDATE between @MIN and @MAX and WG1=1
)


Select 
	1,
	p.NAME,
	'',
	'',
	''

From Position p
--inner join Census c
--on c.WG1 = wh.WG1

--where ss.WG1=1 

UNION

Select 
	2,
	sch.shifttime,
	sch.Staff,
	sch.SchHours,
	wh.whours

From Sch_staff sch
inner join Whours wh
on wh.WG3 = sch.WG3
and wh.WG1 = sch.WG1

where sch.WG1=1

order by 1

Content Inventory

• Doc File: content/docs/reports/schedule/daily_posting_report.mdx
• SQL Script: SQL/reports/schedule/daily_posting_report.sql