LogoSupport Hub
ReportsCoverage Budget

Daily Coverage Budget Report

SQL script that generates a detailed daily coverage budget report displaying budget, schedule, and variance data for nurses and aides across multiple units and locations

Parameters

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

Report Structure

The report is organized hierarchically:

  1. Facility totals - Overall facility metrics including beds, census, occupancy
  2. Staff type totals - Aggregated metrics for nurses and aides
  3. Unit-specific breakdowns - Individual unit metrics including:
    • Petoskey
    • Traverse C
    • Traverse D
    • Ambassador
    • Mackinaw

Key Metrics

For each section, the report includes:

  • Census metrics:

    • Beds
    • Census
    • Occupancy rates

  • Nursing metrics:

    • Total HPPD (Hours Per Patient Day)
    • Total Budget
    • Total Scheduled Hours
    • Total Worked Hours
    • Schedule Variance
    • Worked Variance

  • Aide metrics:

    • Total HPPD
    • Total Budget
    • Total Scheduled Hours
    • Total Worked Hours
    • Schedule Variance
    • Worked Variance

Coverage Types

The report references numerous coverage types from the COVBUDGETS table, including:

  • General metrics (types 1-6)
  • Nurse metrics (types 14-16, 23, 39-44)
  • Aide metrics (types 19-21, 24, 39-42)
  • Unit-specific metrics (types 25-38, 43-46)

Technical Implementation

The script utilizes:

  • Fixed location ID (WG1=9)
  • Multiple UNION statements to combine different sections
  • Extensive subqueries for metric calculations
  • Rounding to 2 decimal places for all numeric values
  • Numerical ordering (column 1) to control result sequence

T-SQL

daily_coverage_budget_report.sql
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='8/1/2022'
SET @MAX='8/1/2022'


Select 1,w1.name as Location, 
	(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=1 and c1.WG1=w1.WGNUM) as Beds,
	(Select ITEMDATA1 from COVBUDGETS c2 where c2.EVENTDATE between @MIN and @MAX and c2.COVERAGETYPE=2 and c2.WG1=w1.WGNUM) as Census,
	Convert(varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=3 and c3.WG1=w1.WGNUM),2)) as 'Occupancy',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=6 and c3.WG1=w1.WGNUM),2) as 'Total HPPD',
	Convert(varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=7 and c3.WG1=w1.WGNUM),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=39 and c3.WG1=w1.WGNUM),2)+	
	Round((Select ITEMFLOAT1 from COVBUDGETS c4 where c4.EVENTDATE between @MIN and @MAX and c4.COVERAGETYPE=41 and c4.WG1=w1.WGNUM),2) as 'Total Sch Hrs',	
	Round((Select ITEMFLOAT1 from COVBUDGETS c4 where c4.EVENTDATE between @MIN and @MAX and c4.COVERAGETYPE=40 and c4.WG1=w1.WGNUM),2)+
	Round((Select ITEMFLOAT1 from COVBUDGETS c4 where c4.EVENTDATE between @MIN and @MAX and c4.COVERAGETYPE=42 and c4.WG1=w1.WGNUM),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=15 and c3.WG1=w1.WGNUM),2)+	
	Round((Select ITEMFLOAT1 from COVBUDGETS c4 where c4.EVENTDATE between @MIN and @MAX and c4.COVERAGETYPE=20 and c4.WG1=w1.WGNUM),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=16 and c3.WG1=w1.WGNUM),2)+	
	Round((Select ITEMFLOAT1 from COVBUDGETS c4 where c4.EVENTDATE between @MIN and @MAX and c4.COVERAGETYPE=21 and c4.WG1=w1.WGNUM),2) as 'Total Wrk Variance'

From WORKGROUP1 w1

Where w1.WGNUM=9

UNION
Select 2,'','','','Nurse',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=23 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=14 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=41 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=42 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=15 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=16 and c3.WG1=9),2) as 'Total Wrk Variance'

/*UNION
Select 2,'','','','','','AM/1st Shift',
	Round((select Sum(Hours)/60 from SCHEDULES where wg1=9 and wg2=1 and wg3 IN (90,139,166,167) and WG6=2 and schtype=0 and SCHDATE between @MIN and @MAX),2),
	Round((select Sum(Hours) from PAYDESHIST where wg1=9 and wg2=1 and wg3 IN (90,139,166,167) and WG6 in (1,2) and PAYDESnum IN (1,2) and EVENTDATE between @MIN and @MAX),2),'',''
UNION
Select 2,'','','','','','2nd Shift',
	Round((select Sum(Hours)/60 from SCHEDULES where wg1=9 and wg2=1 and wg3 IN (90,139,166,167) and WG6=3 and schtype=0 and SCHDATE between @MIN and @MAX),2),
	Round((select Sum(Hours) from PAYDESHIST where wg1=9 and wg2=1 and wg3 IN (90,139,166,167) and WG6=3 and PAYDESnum IN (1,2) and EVENTDATE between @MIN and @MAX),2),'',''
UNION
Select 2,'','','','','','PM/3rd Shift',
	Round((select Sum(Hours)/60 from SCHEDULES where wg1=9 and wg2=1 and wg3 IN (90,139,166,167) and WG6=4 and schtype=0 and SCHDATE between @MIN and @MAX),2),
	Round((select Sum(Hours) from PAYDESHIST where wg1=9 and wg2=1 and wg3 IN (90,139,166,167) and WG6=4 and PAYDESnum IN (1,2) and EVENTDATE between @MIN and @MAX),2),'',''
*/

UNION	
Select 3,'','','','Aides',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=24 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=19 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=39 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=40 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=20 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=21 and c3.WG1=9),2) as 'Total Wrk Variance'

------------------Seperator Row
UNION
Select 4,'','','','','','','','','',''

--------------------Petoskey
UNION

Select 5,Right(w5.name,8),
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=25 and c1.WG5=w5.WGNUM and c1.WG1=9) as Beds,
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=26 and c1.WG5=w5.WGNUM and c1.WG1=9) as Census,
'','','','','','',''

From WORKGROUP5 w5
where w5.WGNUM=2 

UNION

Select 6,'','','','Nurse',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=27 and c3.WG5=2 and c3.WG1=9),2) as 'Total HPPD',
	Convert(varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=30 and c3.WG5=2 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=43 and c3.WG5=2 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=44 and c3.WG5=2 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=31 and c3.WG5=2 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=32 and c3.WG5=2 and c3.WG1=9),2) as 'Total Wrk Variance'

UNION

Select 7,'','','','Aides',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=33 and c3.WG5=2 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=36 and c3.WG5=2 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=45 and c3.WG5=2 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=46 and c3.WG5=2 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=37 and c3.WG5=2 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=38 and c3.WG5=2 and c3.WG1=9),2) as 'Total Wrk Variance'
---------------------Petoskey END

---------------------Traverse C
UNION

Select 8,Right(w5.name,12),
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=25 and c1.WG5=w5.WGNUM and c1.WG1=9) as Beds,
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=26 and c1.WG5=w5.WGNUM and c1.WG1=9) as Census,
'','','','','','',''

From WORKGROUP5 w5
where w5.WGNUM=4 

UNION

Select 9,'','','','Nurse',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=27 and c3.WG5=4 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=30 and c3.WG5=4 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=43 and c3.WG5=4 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=44 and c3.WG5=4 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=31 and c3.WG5=4 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=32 and c3.WG5=4 and c3.WG1=9),2) as 'Total Wrk Variance'

UNION

Select 10,'','','','Aides',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=33 and c3.WG5=4 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=36 and c3.WG5=4 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=45 and c3.WG5=4 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=46 and c3.WG5=4 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=37 and c3.WG5=4 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=38 and c3.WG5=4 and c3.WG1=9),2) as 'Total Wrk Variance'
---------------------Traverse C END 

---------------------Traverse D
UNION

Select 11,Right(w5.name,12),
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=25 and c1.WG5=w5.WGNUM and c1.WG1=9) as Beds,
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=26 and c1.WG5=w5.WGNUM and c1.WG1=9) as Census,
'','','','','','',''

From WORKGROUP5 w5
where w5.WGNUM=5 

UNION

Select 12,'','','','Nurse',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=27 and c3.WG5=5 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=30 and c3.WG5=5 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=43 and c3.WG5=5 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=44 and c3.WG5=5 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=31 and c3.WG5=5 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=32 and c3.WG5=5 and c3.WG1=9),2) as 'Total Wrk Variance'

UNION

Select 13,'','','','Aides',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=33 and c3.WG5=5 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=36 and c3.WG5=5 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=45 and c3.WG5=5 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=46 and c3.WG5=5 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=37 and c3.WG5=5 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=38 and c3.WG5=5 and c3.WG1=9),2) as 'Total Wrk Variance'
---------------------Traverse D END 

---------------------Ambassador
UNION

Select 14,Right(w5.name,10),
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=25 and c1.WG5=w5.WGNUM and c1.WG1=9) as Beds,
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=26 and c1.WG5=w5.WGNUM and c1.WG1=9) as Census,
'','','','','','',''

From WORKGROUP5 w5
where w5.WGNUM=6 

UNION

Select 15,'','','','Nurse',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=27 and c3.WG5=6 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=30 and c3.WG5=6 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=43 and c3.WG5=6 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=44 and c3.WG5=6 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=31 and c3.WG5=6 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=32 and c3.WG5=6 and c3.WG1=9),2) as 'Total Wrk Variance'

UNION

Select 16,'','','','Aides',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=33 and c3.WG5=6 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=36 and c3.WG5=6 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=45 and c3.WG5=6 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=46 and c3.WG5=6 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=37 and c3.WG5=6 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=38 and c3.WG5=6 and c3.WG1=9),2) as 'Total Wrk Variance'
---------------------Ambassador END 

---------------------Mackinaw
UNION

Select 17,Right(w5.name,10),
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=25 and c1.WG5=w5.WGNUM and c1.WG1=9) as Beds,
(Select ITEMDATA1 from COVBUDGETS c1 where c1.EVENTDATE between @MIN and @MAX and c1.COVERAGETYPE=26 and c1.WG5=w5.WGNUM and c1.WG1=9) as Census,
'','','','','','',''

From WORKGROUP5 w5
where w5.WGNUM=7 

UNION

Select 18,'','','','Nurse',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=27 and c3.WG5=7 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=30 and c3.WG5=7 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=43 and c3.WG5=7 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=44 and c3.WG5=7 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=31 and c3.WG5=7 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=32 and c3.WG5=7 and c3.WG1=9),2) as 'Total Wrk Variance'

UNION

Select 19,'','','','Aides',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=33 and c3.WG5=7 and c3.WG1=9),2) as 'Total HPPD',
	Convert(Varchar,Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=36 and c3.WG5=7 and c3.WG1=9),2)) as 'Total Budget',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=45 and c3.WG5=7 and c3.WG1=9),2) as 'Total Sch Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=46 and c3.WG5=7 and c3.WG1=9),2) as 'Total Wrk Hrs',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=37 and c3.WG5=7 and c3.WG1=9),2) as 'Total Sch Variance',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=38 and c3.WG5=7 and c3.WG1=9),2) as 'Total Wrk Variance'
---------------------Mackinaw END 

----Report Order
Order by 1

Content Inventory

  • Doc File: content/docs/reports/coverage_budget/daily_coverage_budget_report.mdx
  • SQL Script: SQL/reports/coverage_budget/daily_coverage_budget_report.sql

Coverage Budget Report

SQL script that generates a comprehensive weekly coverage budget report with scheduled coverage, budgeted hours, and employee schedules by location and department

Daily Coverage Budget Report (Version 2)

Optimized and enhanced version of the daily coverage budget report that includes additional HPPD metrics for scheduled and worked hours

On this page

Parameters
Report Structure
Key Metrics
Coverage Types
Technical Implementation
T-SQL
Content Inventory