VW_GMLI

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

facility_code

varchar

null

facility_name

varchar

300

null

division

varchar

null

reporting_month

date

√

null

reporting_day

date

√

null

lbound_total_reimbursement

money

21,4

√

null

avg_total_reimbursement

money

21,4

√

null

median_total_reimbursement

money

21,4

√

null

ubound_total_reimbursement

money

21,4

√

null

total_census_days

int

√

null

unknown_census_days

int

√

null

therapy_census

int

null

pharm_total_cost

money

21,4

√

null

all_hourly_wages

decimal

40,6

√

null

overtime_hourly_wages

decimal

40,6

√

null

covid_19_hourly_wages_excluding_hazard_pay

decimal

40,6

√

null

total_therapy_cost

decimal

40,13

√

null

View Definition

CREATE VIEW dbo.VW_GMLI AS

/*********************************************************************
  Overall summary view for Gross Margin Leading Indicator.
  To keep things simple, as-of date "freshness" indication is
  handled in a separate view.

  IMPORTANT: Each facility/reporting month/date should be summarized
             in a single overall row. Any subtotals (e.g. revenue by
             Medicare A) MUST be handled outside this view. This is
             because any incongruent subgroups across the various
             components. Also, the intent is to display data inclusive
             of the prior day, relative to when this view's data is
             retrieved.

  DEPRECATION NOTICE:
  This view is in the process of being deprecated, to be replaced by
  VW_GMLI_2. The reason for creating a new view is to allow transition
  time without disruption. VW_GMLI_2 will differ from the current view
  in that it will contain one row per facility id, instead of a single
  all-encompassing rollup that encompasses all facility types.

  TESTS
  select *
  from dbo.VW_GMLI
  where reporting_month in ('2019-09-01', '2019-10-01', '2019-11-01')
  order by facility_name, reporting_month, reporting_day
*********************************************************************/

    WITH pharm AS (
        SELECT facref.ReportAcctFacCode,
               invoice.Amount,
               invoice.DispenseDate AS cost_date,
               DATEFROMPARTS(InvoiceYearMonth / 100, InvoiceYearMonth - (InvoiceYearMonth / 100) * 100, 1) AS reporting_month
        FROM dbo.PPPL_InvoiceDetail AS invoice
             JOIN dbo.AutoPop_AvaReportingFacPPPLMembers AS facref
                  ON invoice.FacID = facref.PPPLFacId
                     AND facref.PPPLFacId LIKE 'S%'
        WHERE InvoiceYearMonth < 201912 -- Use historical invoice data in lieu of unbilled, prior to first unbilled feed month
        UNION ALL
        SELECT facref.ReportAcctFacCode,
               unbilled.Amount,
               CONVERT(DATE, unbilled.Posted)                            AS cost_date,
               DATEADD(MM, -1, DATEADD(DD, 1, unbilled.MonthEndInvDate)) AS reporting_month
        FROM dbo.PPPL_Unbilled AS unbilled
             JOIN dbo.AutoPop_AvaReportingFacPPPLMembers AS facref
                  ON unbilled.FacID = facref.PPPLFacId
                     AND facref.PPPLFacId LIKE 'S%'
        WHERE unbilled.Posted < CONVERT(DATE, GETDATE())
          AND unbilled.Posted >= '2019-12-01' --Month when unbilled feed went live
    ),
         pharm_sum AS (
             /* NOTE: Assign "out of band" cost dates to beginning or end of month as appropriate */
             SELECT ReportAcctFacCode,
                    SUM(Amount)            AS total_amount,
                    CASE
                        WHEN cost_date < reporting_month THEN reporting_month
                        WHEN cost_date >= DATEADD(MM, 1, reporting_month) THEN EOMONTH(reporting_month)
                        ELSE cost_date END AS cost_date,
                    reporting_month
             FROM pharm
             GROUP BY ReportAcctFacCode,
                      CASE
                          WHEN cost_date < reporting_month THEN reporting_month
                          WHEN cost_date >= DATEADD(MM, 1, reporting_month) THEN EOMONTH(reporting_month)
                          ELSE cost_date END,
                      reporting_month
         ),
         wages AS (
             SELECT kra_workdate,
                    ReportAcctFacCode AS facility_code,
                    pay_type,
                    total_wages,
                    DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(kra_workdate))) AS reporting_month,
                    CAST(0 AS TINYINT) AS c19_flag
             FROM dbo.Kronos_HourlyWages
             WHERE kra_workdate < CONVERT(DATE, GETDATE())
             UNION ALL
             SELECT kra_workdate,
                    ReportAcctFacCode AS facility_code,
                    pay_code AS pay_type,
                    total_wages,
                    DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(kra_workdate))) AS reporting_month,
                    CAST(1 AS TINYINT) AS c19_flag
             FROM dbo.Kronos_C19HourlyWages as c
             WHERE kra_workdate < CONVERT(DATE, GETDATE())
         ),
         wages_sum AS (
             SELECT facility_code,
                    reporting_month,
                    kra_workdate,
                    sum(CASE WHEN pay_type = 'Overtime' THEN total_wages ELSE 0 END) AS overtime_wages,
                    sum(CASE WHEN c19_flag = 1 THEN total_wages ELSE 0 END)          AS covid19_wages,
                    sum(total_wages)                                                 AS total_wages
             FROM wages
             GROUP BY facility_code,
                      reporting_month,
                      kra_workdate
         ),
         therapy AS (
             SELECT facref.ReportAcctFacCode,
                    rev.report_month, -- Calculated during ETL because it can't be derived from resultset itself
                    rev.report_day,
                    SUM(rev.Amount) AS total_revenue
             FROM dbo.ROX_Patient_Billings AS rev
                  JOIN dbo.AutoPop_AvaReportingFacROXMembers AS facref
                       ON rev.ExportKey = facref.ROXExportKey
             WHERE rev.report_day < CONVERT(DATE, GETDATE())
             GROUP BY facref.ReportAcctFacCode,
                      rev.report_month,
                      rev.report_day,
                      facref.ReportAcctFacCode
         ),
        revenue AS (
            SELECT fac.ReportAcctFacCode,
                   reimb.FacilityHealthCareType,
                   DATEADD(mm, -1, DATEADD(dd, 1, EOMONTH(CONVERT(DATE, reimb.CensusDate)))) AS reporting_month,
                   reimb.CensusDate,
                   reimb_est.lbound_reimb,
                   reimb_est.mean_reimb,
                   reimb_est.ubound_reimb,
                   reimb_est.median_reimb,
                   CASE reimb_est.est_method WHEN 'actual' THEN 0 ELSE 1 END AS unk_flag
            FROM dbo.PCC_Census_Reimb AS reimb
                 JOIN dbo.PCC_Cache_FacID AS faccache
                      ON reimb.FacilityID = faccache.FacilityID
                            AND faccache.FacilityType = 'SNF'
                 JOIN dbo.PCC_Census_Reimb_Est AS reimb_est
                      ON reimb.CensusId = reimb_est.CensusId
                            AND reimb.CensusDate = reimb_est.CensusDate
                 JOIN dbo.AutoPop_AvaReportingFacMembers AS facmem
                      ON reimb.FacilityCode = facmem.AcctFacCode
                 JOIN dbo.AutoPop_AvaReportingFac AS fac
                      ON facmem.ReportAcctFacCode = fac.ReportAcctFacCode
            WHERE reimb.CensusDate < CONVERT(DATE, GETDATE())
                  AND reimb.CensusStatusCode = 'A' -- Only "Active" census days
        ),
         revenue_sum AS (
             SELECT ReportAcctFacCode,
                    reporting_month,
                    CensusDate,
                    SUM(lbound_reimb) AS lbound_total_reimbursement,
                    SUM(mean_reimb) AS avg_total_reimbursement,
                    SUM(median_reimb) AS median_total_reimbursement,
                    SUM(ubound_reimb) AS ubound_total_reimbursement,
                    COUNT(*) AS total_census_days,
                    SUM(unk_flag) AS unknown_census_days
             FROM revenue
             WHERE CensusDate < CONVERT(DATE, GETDATE())
             GROUP BY ReportAcctFacCode,
                      FacilityHealthCareType,
                      reporting_month,
                      CensusDate
         ),
          therapy_census AS (
              SELECT pb.DateOfService AS therapy_date,
                     roxfac.ReportAcctFacCode,
                     COUNT(DISTINCT pb.Resident_ID) AS th_census
              FROM dbo.ROX_Patient_Billings AS pb
                   JOIN dbo.AutoPop_AvaReportingFacROXMembers AS roxfac
                        ON pb.ExportKey = roxfac.ROXExportKey
              GROUP BY pb.DateOfService,
                       roxfac.ReportAcctFacCode)

    SELECT fac.ReportAcctFacCode AS facility_code,
           fac.DisplayName AS facility_name,
           fac.division,
           revenue_sum.reporting_month, --Use revenue (i.e. presence of PCC reimbursement data) as reference point for reporting month
           CONVERT(DATE, revenue_sum.CensusDate) AS reporting_day,
           revenue_sum.lbound_total_reimbursement,
           revenue_sum.avg_total_reimbursement,
           revenue_sum.median_total_reimbursement,
           revenue_sum.ubound_total_reimbursement,
           revenue_sum.total_census_days,
           revenue_sum.unknown_census_days,
           ISNULL(therapy_census.th_census, 0) AS therapy_census,
           pharm_sum.total_amount AS pharm_total_cost,
           wages_sum.total_wages AS all_hourly_wages,
           wages_sum.overtime_wages AS overtime_hourly_wages,
           wages_sum.covid19_wages AS covid_19_hourly_wages_excluding_hazard_pay,
           therapy.total_revenue AS total_therapy_cost
    FROM dbo.AutoPop_AvaReportingFac AS fac --Driver table
    LEFT JOIN revenue_sum --Auxiliary driver table. LEFT JOINs are to ensure all facilities represented even if there's missing data
        ON fac.ReportAcctFacCode = revenue_sum.ReportAcctFacCode
    LEFT JOIN wages_sum
        ON revenue_sum.ReportAcctFacCode = wages_sum.facility_code
            AND revenue_sum.reporting_month = wages_sum.reporting_month
            AND revenue_sum.CensusDate = wages_sum.kra_workdate
    LEFT JOIN therapy
        ON revenue_sum.ReportAcctFacCode = therapy.ReportAcctFacCode
            AND revenue_sum.reporting_month = therapy.report_month
            AND revenue_sum.CensusDate = therapy.report_day
    LEFT JOIN therapy_census
        ON revenue_sum.ReportAcctFacCode = therapy_census.ReportAcctFacCode
            AND revenue_sum.CensusDate = therapy_census.therapy_date
    LEFT JOIN pharm_sum
        ON revenue_sum.ReportAcctFacCode = pharm_sum.ReportAcctFacCode
            AND revenue_sum.reporting_month = pharm_sum.reporting_month
            AND revenue_sum.CensusDate = pharm_sum.cost_date
    WHERE fac.Division = 'SNF'
        AND fac.IsActive = 'Y'

Columns

View Definition

Possibly Referenced Tables/Views