VW_GMLI_CurrMonth_RevTrend

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

asof_date

date

null

facility_code

varchar

null

facility_name

varchar

300

null

prior_closed_month

date

√

null

prior_close_total_reimb

money

21,4

√

null

prior_close_total_census_days

int

√

null

lbound_reimb_total

money

21,4

√

null

median_reimb_total

money

21,4

√

null

ubound_reimb_total

money

21,4

√

null

total_census_days

int

√

null

unknown_census_days

int

√

null

curr_open_month

date

√

null

View Definition

CREATE VIEW dbo.VW_GMLI_CurrMonth_RevTrend AS

    /* This is a helper view on top of VW_GMLI_History which shows current month history by day,
       relative to prior closed month. Current and prior closed month are determined (calculated in view)
       based on "today", whenever the view is accessed.

       NOTE: "current month" is the current open accounting month. "prior month" is the most recently
       closed accounting month.

       For Avamere Living: Month-end close occurs at 5pm on the 6th business day of the following month.

       However, to keep things simple here, we use the calendar 13th of the following month. This is
       a conservative date which takes into account weekends and holidays.
     */

    WITH month_ref AS (
        SELECT TOP 1 prior_closed_month, curr_open_month
        FROM dbo.VW_GMLI_History AS hist
    ),
         last_close AS (
             SELECT gmli.facility_code,
                    SUM(gmli.ubound_total_reimbursement) AS total_reimbursement,
                    SUM(gmli.total_census_days) AS total_census_days,
                    MAX(prior_closed_month) AS closed_month
             FROM dbo.VW_GMLI AS gmli
                  JOIN month_ref
                       ON gmli.reporting_month = month_ref.prior_closed_month
             GROUP BY gmli.facility_code
         )

    /* NOTE: For each of the MAX() values below, there is actually only only a single value
       within each asof_date/facility group. The MAX() function is used as a shortcut to avoid
       a verbose GROUP BY clause. */
    SELECT hist.asof_date,
           hist.facility_code,
           hist.facility_name,
           MAX(prior_closed_month) AS prior_closed_month,
           MAX(last_close.total_reimbursement) AS prior_close_total_reimb,
           MAX(last_close.total_census_days) AS prior_close_total_census_days,
           MAX(hist.lbound_reimb_total) AS lbound_reimb_total,
           MAX(hist.median_reimb_total) AS median_reimb_total,
           MAX(hist.ubound_reimb_total) AS ubound_reimb_total,
           MAX(hist.total_census_days) AS total_census_days,
           MAX(hist.unknown_census_days) AS unknown_census_days,
           MAX(hist.curr_open_month) AS curr_open_month
    FROM VW_GMLI_History AS hist
         JOIN last_close
              ON hist.facility_code = last_close.facility_code
                  AND hist.reporting_month = hist.curr_open_month
    GROUP BY hist.facility_code,
             hist.facility_name,
             hist.asof_date

Columns

View Definition

Possibly Referenced Tables/Views