VW_GMLI_2

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

parent_facility_code

varchar

null

facility_code

varchar

√

null

facility_id

int

√

null

ROX_Cache_Fac.Facility_ID

Implied ConstraintR

facility_name

varchar

300

null

facility_type

varchar

254

√

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_wages_and_reimbursements

decimal

40,6

√

null

total_therapy_cost

decimal

40,13

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_GMLI_2 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.

  AMENDMENT: To allow fine-grained subtotals where possible,
             this view now splits totals by PCC Facility Id.
             However, a grand total for labor is repeated
             across all "sub" rows for a given "building".
             ReportAcctFacCode is used as the "parent" facility code
             that gathers associated subtotals.

  TESTS
  select *
  from dbo.VW_GMLI_2
  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,
               /* Derive facility type from PPPL internal Avamere identifier */
               CASE WHEN facref.PPPLFacId LIKE 'S%' THEN 'SNF' ELSE 'ALF' END AS facility_type,
               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
        WHERE InvoiceYearMonth < 201912 -- Use historical invoice data in lieu of unbilled, prior to first unbilled feed month
        UNION ALL
        SELECT facref.ReportAcctFacCode,
               /* Derive facility type from PPPL internal Avamere identifier */
               CASE WHEN facref.PPPLFacId LIKE 'S%' THEN 'SNF' ELSE 'ALF' END AS facility_type,
               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
        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,
                    facility_type,
                    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,
                      facility_type,
                      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,
                    pay_type,
                    total_wages,
                    DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(kra_workdate))) AS reporting_month,
                    CAST(CASE WHEN job_code IN ('EMS001') THEN 1 ELSE 0 END AS TINYINT) AS c19_flag
             FROM dbo.Kronos_HourlyWages
             WHERE kra_workdate < CONVERT(DATE, GETDATE())
             UNION ALL
             SELECT kra_workdate,
                    ReportAcctFacCode,
                    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())
             UNION ALL
             SELECT md.KRAWorkDate AS kra_workdate,
                    facmem.ReportAcctFacCode,
                    md.PayCodeName AS pay_type,
                    md.TotalMoneyAmount AS total_wages,
                    DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(md.KRAWorkDate))) AS reporting_month,
                    CAST(1 AS TINYINT) AS c19_flag
             FROM dbo.Kronos_C19MoneyDaily AS md
             JOIN dbo.AutoPop_AvaReportingFacMembers AS facmem
                ON md.AcctFacCode = facmem.AcctFacCode
             WHERE md.KRAWorkDate < CONVERT(DATE, GETDATE())
         ),
         wages_sum AS (
             SELECT ReportAcctFacCode,
                    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 ReportAcctFacCode,
                      reporting_month,
                      kra_workdate
         ),
         therapy AS (
             SELECT facref.ReportAcctFacCode,
                    facref.FacilityType,
                    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,
                      facref.FacilityType,
                      rev.report_month,
                      rev.report_day,
                      facref.ReportAcctFacCode
         ),
        revenue AS (
            SELECT fac.ReportAcctFacCode,
                   reimb.FacilityCode AS AcctFacCode,
                   faccache.FacilityID,
                   faccache.FacilityType,
                   DATEADD(mm, -1, DATEADD(dd, 1, EOMONTH(CONVERT(DATE, reimb_est.CensusDate)))) AS reporting_month,
                   reimb_est.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_Est AS reimb_est
                 JOIN dbo.PCC_Census_Reimb AS reimb
                      ON reimb_est.CensusId = reimb.CensusId
                          AND reimb_est.CensusDate = reimb.CensusDate
                 JOIN dbo.PCC_Cache_FacID AS faccache
                      ON reimb_est.FacilityID = faccache.FacilityID
                 JOIN dbo.AutoPop_AvaReportingFacMembers AS facmem
                      ON faccache.FacilityCode = facmem.AcctFacCode
                 JOIN dbo.AutoPop_AvaReportingFac AS fac
                      ON facmem.ReportAcctFacCode = fac.ReportAcctFacCode
            WHERE reimb_est.CensusDate < CONVERT(DATE, GETDATE())
                AND (reimb.CensusStatusCode = 'A' OR faccache.FacilityType <> 'SNF')
        ),
         revenue_sum AS (
             SELECT ReportAcctFacCode,
                    AcctFacCode,
                    FacilityID,
                    FacilityType,
                    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,
                      AcctFacCode,
                      FacilityID,
                      FacilityType,
                      reporting_month,
                      CensusDate
         ),
          therapy_census AS (
              SELECT pb.DateOfService AS therapy_date,
                     roxfac.ReportAcctFacCode,
                     roxfac.FacilityType,
                     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.FacilityType,
                       roxfac.ReportAcctFacCode)

    SELECT fac.ReportAcctFacCode AS parent_facility_code,
           revenue_sum.AcctFacCode AS facility_code,
           revenue_sum.FacilityID AS facility_id,
           fac.DisplayName AS facility_name,
           revenue_sum.FacilityType AS facility_type,
           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_wages_and_reimbursements,
           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
        /* Wages are "duplicated" across all facility types.
           There is no facility type correspondence in Kronos. */
        ON revenue_sum.ReportAcctFacCode = wages_sum.ReportAcctFacCode
            AND revenue_sum.reporting_month = wages_sum.reporting_month
            AND revenue_sum.CensusDate = wages_sum.kra_workdate
    /* For the remaining joins: we relay on facility type being a 1:1 surrogate for PCC facility id.
       (The alternative is to maintain that data in the AutoPop lookup tables - cumbersome.)
       For some ILFs, there is more than one facilty id for the "same" ILF e.g. Bethany.
       But we since no pharmacy or therapy is associated with ILFs, that shouldn't matter. */
    LEFT JOIN therapy
        ON revenue_sum.ReportAcctFacCode = therapy.ReportAcctFacCode
            AND revenue_sum.FacilityType = therapy.FacilityType
            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.FacilityType = therapy_census.FacilityType
            AND revenue_sum.CensusDate = therapy_census.therapy_date
    LEFT JOIN pharm_sum
        ON revenue_sum.ReportAcctFacCode = pharm_sum.ReportAcctFacCode
            AND revenue_sum.FacilityType = pharm_sum.facility_type
            AND revenue_sum.reporting_month = pharm_sum.reporting_month
            AND revenue_sum.CensusDate = pharm_sum.cost_date
    WHERE fac.IsActive = 'Y'

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views