VW_ROX_Revenue

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

facility_id

varchar

null

CEU360_Users.id

Implied ConstraintR

report_month

date

null

report_day

date

null

Facility

varchar

100

null

Region

varchar

100

√

null

Area

varchar

100

√

null

payer_type

varchar

100

√

null

payer_name

varchar

100

√

null

ot_revenue

float

√

null

pt_revenue

float

√

null

st_revenue

float

√

null

total_revenue

float

√

null

therapy_days

int

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_ROX_Revenue AS

/*********************************************************************
  View intended specifically for tracking Infinity revenue and
  associated "therapy census" (which includes per diem billing
  with or without therapy)

  This is for all INF facilities (not only Avamere facs).

  TESTS
  select sum(total_revenue)
  from dbo.VW_ROX_Revenue
  where Facility = 'Able Palms Home Health'
   and report_month = '2020-02-01';
  --25310

  select sum(therapy_days)
  from dbo.VW_ROX_Revenue
  where Facility = 'Able Palms Home Health'
    and report_month = '2020-02-01';
  --323

  --TIF (time in facility) site
  select sum(total_revenue)
  from dbo.VW_ROX_Revenue
  where Facility = 'Myrtle Point Care Center'
   and report_month = '2020-02-01';
  --15107

  select sum(therapy_days)
  from dbo.VW_ROX_Revenue
  where Facility = 'Myrtle Point Care Center'
    and report_month = '2020-02-01';
  --null
*********************************************************************/

WITH fac AS (
    SELECT ExportKey,
           f.FacName,
           RANK() OVER (PARTITION BY ExportKey ORDER BY IsInactive, facLastModifiedDate, Facility_ID DESC) AS rank_num,
           IsOutPatient,
           facLastModifiedDate,
           Region,
           Area
    FROM dbo.ROX_Cache_Fac AS f
    WHERE ExportKey <> ''
)

, dates AS (
    /* Shortcut to get a date reference set to use with special handling such as "Vibra" */
    SELECT DISTINCT report_month,
                    report_day
    FROM dbo.ROX_Monthly_Revenue
    -- Implicitly will be through "yesterday"
)

, monthly_rev AS (
    SELECT rev.ExportKey,
           rev.report_month,
           rev.report_day,
           rev.Facility,
           fac.Region,
           fac.Area,
           /* This CASE is the whole point of the CTE: These two payer plans can map to any
              number of payers, so they are standardized as N/A. Otherwise we'll get duplication. */
           CASE WHEN rev.PayerPlan IN ('', 'Non-Patient Revenue') THEN 'N/A' ELSE rev.Payer END AS Payer,
           rev.PayerPlan,
           rev.PTRevenue,
           rev.OTRevenue,
           rev.STRevenue,
           rev.TotRevenue
    FROM dbo.ROX_Monthly_Revenue AS rev
    JOIN fac
    ON rev.ExportKey = fac.ExportKey
    /* NOTE: Village Health & Reab is intentionally NOT excluded here. REASON: They do "regular" building
             PLUS a supplemental amount a la Vibra. See below. */
    AND rev.Facility NOT IN ('Vibra')
    AND fac.rank_num = 1

    UNION

    /* Specially handled facilities (e.g. Vibra). NOTE: Must be EXCLUDED from top part of the union. */
    SELECT fac.ExportKey,
           dates.report_month,
           dates.report_day,
           fac.FacName AS Facility,
           fac.Region,
           fac.Area,
           'Managed Care Part-B' AS Payer,
           'Census-Daily Average' AS PayerPlan,
           0 AS PTRevenue,
           0 AS OTRevenue,
           0 AS STRevenue,
           /* Special handling for Vibra per Carly Lemmo / Nancy Calhoon : $64K per month */
           ROUND(64000.0 / DAY(EOMONTH(dates.report_month)), 2) AS TotRevenue
      FROM fac
      CROSS JOIN dates
      WHERE fac.FacName = 'Vibra'
      AND fac.rank_num = 1
      AND dates.report_month < '2022-01-01' -- Per Carly Vibra no longer active since Jan 2022
    UNION
    SELECT fac.ExportKey,
           dates.report_month,
           dates.report_day,
           fac.FacName AS Facility,
           fac.Region,
           fac.Area,
           'RNA Services' AS Payer,
           'RNA Services' AS PayerPlan,
           0 AS PTRevenue,
           0 AS OTRevenue,
           0 AS STRevenue,
           /* Special handling for Village Health & Rehab per Carly Lemmo - this amount seems low and needs confirmation. */
           ROUND(3200.0 / DAY(EOMONTH(dates.report_month)), 2) AS TotRevenue
      FROM fac
      CROSS JOIN dates
      WHERE fac.FacName = 'Village Health & Rehab'
      AND fac.rank_num = 1
    UNION
    /* "Restorative Coordinator" revenue added in per Carly. This is additional revenue, not meant to exclude/override
       anything above. */
    SELECT fac.ExportKey,
           dates.report_month,
           dates.report_day,
           fac.FacName AS Facility,
           fac.Region,
           fac.Area,
           'N/A' AS Payer,
           'Non-Patient Revenue' AS PayerPlan,
           0 AS PTRevenue,
           0 AS OTRevenue,
           0 AS STRevenue,
           /* Special handling for Village Health & Rehab per Carly Lemmo - this amount seems low and needs confirmation. */
           ROUND(2500.0 / DAY(EOMONTH(dates.report_month)), 2) AS TotRevenue
    FROM fac
    CROSS JOIN dates
    /* Per Carly:
       The 4 sites are:
        Village Health & Rehab – 596
        Clermont Park Health Center – 264
        Someren Glen – 265
        Shalom Park – 469
     */
    WHERE fac.ExportKey IN ('264', '265', '469', '596')
    AND fac.rank_num = 1
    AND dates.report_month >= '2021-01-01' -- Filter per Carly
),
    fac_census AS (
         /* NOTE: Distinct patients. Same patient counts once for each discipline */
         SELECT report_month,
                report_day,
                ExportKey,
                PayerName,
                SUM(therapy_days) AS therapy_days
         FROM
         (SELECT report_month,
                report_day,
                ExportKey,
                PayerName,
                Discipline,
                COUNT(DISTINCT Resident_ID) AS therapy_days
         FROM dbo.ROX_Patient_Billings
         GROUP BY report_month,
                  report_day,
                  ExportKey,
                  PayerName,
                  Discipline) AS sub
         GROUP BY report_month,
                  report_day,
                  ExportKey,
                  PayerName
     ),
     payer_lookup AS (
         /* NOTE: Distinctness apparently facility-indepdendent
                  HOWEVER, ExportKey included as safeguard */
         SELECT DISTINCT ExportKey,
                         CASE WHEN PayerPlan IN ('', 'Non-Patient Revenue') THEN 'N/A' ELSE Payer END AS Payer,
                         PayerPlan
         FROM dbo.ROX_Monthly_Revenue
         /*
         REFERENCE QUERIES TO OBSERVE "REVERSE" 1->Many
         --by facility
         select ExportKey, PayerPlan, count(distinct Payer) as total
         from dbo.ROX_Monthly_Revenue
         group by ExportKey, PayerPlan
         having count(distinct Payer) > 1;
         --all facilities
         select PayerPlan, count(distinct Payer) as total
         from dbo.ROX_Monthly_Revenue
         group by PayerPlan
         having count(distinct Payer) > 1;
         */
     )

SELECT monthly_rev.ExportKey AS facility_id,
       monthly_rev.report_month,
       monthly_rev.report_day AS report_day,
       monthly_rev.Facility,
       monthly_rev.Region,
       monthly_rev.Area,
       payer_lookup.payer AS payer_type,
       monthly_rev.PayerPlan AS payer_name,
       SUM(monthly_rev.OTRevenue) AS ot_revenue,
       SUM(monthly_rev.PTRevenue) AS pt_revenue,
       SUM(monthly_rev.STRevenue) AS st_revenue,
       SUM(monthly_rev.TotRevenue) AS total_revenue,
       SUM(fac_census.therapy_days) AS therapy_days
FROM monthly_rev
LEFT JOIN payer_lookup -- LEFT JOIN to accommodate hard-coded special cases e.g. Valley Health & Rehab w/o specific payor
    ON monthly_rev.ExportKey = payer_lookup.ExportKey
        AND monthly_rev.PayerPlan = payer_lookup.PayerPlan
LEFT JOIN fac_census
    ON monthly_rev.ExportKey = fac_census.ExportKey
        AND monthly_rev.PayerPlan = fac_census.PayerName
        AND monthly_rev.report_month = fac_census.report_month
        AND monthly_rev.report_day = fac_census.report_day
GROUP BY monthly_rev.ExportKey,
         monthly_rev.report_month,
         monthly_rev.report_day,
         monthly_rev.Facility,
         monthly_rev.Region,
         monthly_rev.Area,
         payer_lookup.payer,
         monthly_rev.PayerPlan

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views