VW_GMLI_History

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

reporting_month

date

√

null

asof_date

date

null

facility_code

varchar

null

facility_name

varchar

300

null

lbound_reimb_total

money

21,4

√

null

median_reimb_total

money

21,4

√

null

avg_reimb_total

money

21,4

√

null

ubound_reimb_total

money

21,4

√

null

total_census_days

int

√

null

unknown_census_days

int

√

null

curr_month_flag

varchar

null

prior_month_flag

varchar

null

curr_open_month

date

√

null

prior_closed_month

date

√

null

View Definition

CREATE VIEW dbo.VW_GMLI_History AS

    /* Reminder: Reimbursements are current through prior day */
    WITH refdates_cal_pre AS (
        /* Simple date math to see where we are on the calendar */
        SELECT DATEADD(DD, -1, GETDATE()) AS yesterday,
               DATEADD(MM, -1, DATEADD(DD, -1, GETDATE())) AS last_month,
               DATEADD(MM, -2, DATEADD(DD, -1, GETDATE())) AS two_months_ago
    ),
         /* Convert reference dates to first of month */
         refdates_cal AS (
             SELECT DATEPART(DD, yesterday) AS yest_daynum,
                    DATEFROMPARTS(YEAR(yesterday), MONTH(yesterday), 1) AS this_month,
                    DATEFROMPARTS(YEAR(last_month), MONTH(last_month), 1) AS last_month,
                    DATEFROMPARTS(YEAR(two_months_ago), MONTH(two_months_ago), 1) AS two_months_ago
             FROM refdates_cal_pre
         ),
         /* Find where we are in the accounting month-end close cycle */
         refdates_acct AS (
             SELECT CASE WHEN yest_daynum > 13 THEN refdates_cal.last_month
                         ELSE refdates_cal.two_months_ago END AS prior_closed_month,
                    CASE WHEN yest_daynum > 13 THEN refdates_cal.this_month
                         ELSE refdates_cal.last_month END AS curr_open_month
             FROM refdates_cal
         )

    SELECT h.reporting_month,
           h.asof_date,
           h.facility_code,
           h.facility_name,
           SUM(h.lbound_total_reimbursement) AS lbound_reimb_total,
           SUM(h.median_total_reimbursement) AS median_reimb_total,
           SUM(h.avg_total_reimbursement) AS avg_reimb_total,
           SUM(h.ubound_total_reimbursement) AS ubound_reimb_total,
           SUM(h.total_census_days) AS total_census_days,
           SUM(h.unknown_census_days) AS unknown_census_days,
           CASE WHEN h.reporting_month = rda.curr_open_month THEN 'Y' ELSE 'N' END AS curr_month_flag,
           CASE WHEN h.reporting_month = rda.prior_closed_month THEN 'Y' ELSE 'N' END AS prior_month_flag,
           MAX(rda.curr_open_month) AS curr_open_month,
           MAX(rda.prior_closed_month) AS prior_closed_month
    FROM dbo.GMLI_History AS h
    CROSS JOIN refdates_acct AS rda
    GROUP BY h.reporting_month,
             h.asof_date,
             h.facility_code,
             h.facility_name,
             CASE WHEN h.reporting_month = rda.curr_open_month THEN 'Y' ELSE 'N' END,
             CASE WHEN h.reporting_month = rda.prior_closed_month THEN 'Y' ELSE 'N' END

Columns

View Definition

Possibly Referenced Tables/Views