LogoSupport Hub
ReportsCoverage Budget

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

Parameters

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

Report Structure

The report has a simplified structure compared to Version 1, focusing on:

  1. Facility totals - Overall facility metrics
  2. Staff type breakdowns - Detailed metrics for:
    • Nurses
    • Aides

Key Metrics

This enhanced version includes additional metrics:

  • Facility metrics:

    • Beds
    • Census
    • Occupancy
    • Target HPPD
    • Total Budget
    • Total Scheduled Hours
    • Total Scheduled HPPD (new)
    • Total Worked Hours
    • Total Worked HPPD (new)
    • Schedule Variance
    • Work Variance

  • Staff-specific metrics (both nurses and aides):

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

Coverage Types

This version uses different coverage types compared to V1, including:

  • Standard metrics (types 1-7)
  • HPPD metrics (types 4-5 for target, 48-49 for scheduled, 51-53 for worked)
  • Variance metrics (types 15-16, 20-21)
  • Scheduled/worked metrics (types 39-42)

Technical Implementation

The script features:

  • Simplified structure with fewer UNION statements
  • New coverage types for HPPD calculations (48-53)
  • More comprehensive HPPD tracking with target vs. scheduled vs. worked
  • Improved organization focusing on facility-wide data
  • Fixed location ID (WG1=9)

Improvements Over V1

  1. Added HPPD calculations for scheduled and worked hours
  2. Renamed "Total HPPD" to "Target HPPD" for clarity
  3. Simplified report structure by removing unit-level sections
  4. Cleaner code organization with fewer queries
  5. More focused metrics for comparing target vs. actual values

T-SQL

daily_coverage_budget_report_v2.sql
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='11/27/2022'
SET @MAX='11/27/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 'Target 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 c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=50 and c3.WG1=w1.WGNUM),2) as 'Total Sch HPPD',
	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=53 and c3.WG1=w1.WGNUM),2) as 'Total Wrk HPPD',
	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=4 and c3.WG1=9),2) as 'Target 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=48 and c3.WG1=9),2) as 'Total Sch HPPD',
	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=51 and c3.WG1=9),2) as 'Total Wrk HPPD',
	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 3,'','','','Aides',
	Round((Select ITEMFLOAT1 from COVBUDGETS c3 where c3.EVENTDATE between @MIN and @MAX and c3.COVERAGETYPE=5 and c3.WG1=9),2) as 'Target 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=49 and c3.WG1=9),2) as 'Total Sch HPPD',
	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=52 and c3.WG1=9),2) as 'Total Wrk HPPD',
	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'

----Report Order
Order by 1

Content Inventory

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

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

Employee Information Reports

Documentation for all employee information report scripts that generate reports about employee details, directories, and related personnel information

On this page

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