VW_GMLI_Freshness

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

reporting_month

date

√

null

revenue_asof

date

√

null

pharm_asof

date

√

null

wages_asof

date

√

null

therapy_asof

date

√

null

View Definition

CREATE VIEW dbo.VW_GMLI_Freshness AS

/*********************************************************************
    Provides a window into data freshness for various components
    of Gross Margin Leading Indicator. This is done separately here
    to create at-a-glance overall freshness by component, at a month level
    (reminder: VW_GMLI is aggregated at a day level).

    "asof_date" means we have data through the as-of date, inclusive.
    The associated date semantics for each data source (revenue, pharm, etc.)
    differ, so we must use care to be consistent.

    TEST
    select *
    from dbo.VW_GMLI_Freshness
    order by reporting_month
*********************************************************************/

    WITH pharm_asof AS (
        SELECT DATEADD(MM, -1, DATEADD(DD, 1, MonthEndInvDate)) AS reporting_month,
               DATEADD(dd, -1, MAX(FileCreationDate))           AS asof_date
        FROM dbo.PPPL_Unbilled
        GROUP BY DATEADD(MM, -1, DATEADD(DD, 1, MonthEndInvDate))
    ),
         wages_asof_pre AS (
             /* Note that asof_date in Kronos_HourlyWages means insertion date,
                so we must deduct a day. */
             SELECT DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(kra_workdate))) AS reporting_month,
                    CONVERT(DATE, DATEADD(DD, -1, asof_date))              AS asof_date
             FROM dbo.Kronos_HourlyWages
         ),
         wages_asof AS (
             SELECT reporting_month,
                    MAX(asof_date) AS asof_date
             FROM wages_asof_pre
             GROUP BY reporting_month
         ),
         therapy_asof AS (
             /* Note: reporting_month here is calculated during upstream ETL,
                because upstream resultset (from ROX proc) contains no date fields */
             SELECT report_month    AS reporting_month,
                    MAX(CONVERT(DATE, DateOfService)) AS asof_date
             FROM dbo.ROX_Patient_Billings
             GROUP BY report_month
         ),
         revenue_asof_pre AS (
             /* As with wages above, asof_datetime in source table actually correlates
                to an insertion date. Also as with wages, we need to deduct a day, since
                prior day's insertion occurs during an ETL which runs after midnight. */
             SELECT DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(CONVERT(DATE, reimb.CensusDate)))) AS reporting_month,
                    CONVERT(DATE, DATEADD(DD, -1, reimb.asof_datetime))                       AS asof_date
             FROM dbo.PCC_Census_Reimb AS reimb
         ),
         revenue_asof AS (
             SELECT reporting_month,
                    MAX(asof_date) AS asof_date
             FROM revenue_asof_pre
             GROUP BY reporting_month
         ),
         /* Current reporting month is based on "today" and correlates to day 1 of the month.
            For example: reporting month associated with 2019-11-28 is 2019-11-01. */
         current_month AS (
                SELECT DATEADD(mm, -1, DATEADD(dd, 1, CONVERT(DATE, EOMONTH(GETDATE())))) AS first_of_month,
                       DATEADD(dd, -1, CONVERT(DATE, GETDATE())) AS expected_fresh_date
         )

    /* Use reimbursement as driver table */
    SELECT rev.reporting_month,
           rev.asof_date     AS revenue_asof,
           pharm.asof_date   AS pharm_asof,
           wages.asof_date   AS wages_asof,
           therapy.asof_date AS therapy_asof
    FROM revenue_asof AS rev
         LEFT JOIN pharm_asof AS pharm
                   ON rev.reporting_month = pharm.reporting_month
         LEFT JOIN wages_asof AS wages
                   ON rev.reporting_month = wages.reporting_month
         LEFT JOIN therapy_asof AS therapy
                   ON rev.reporting_month = therapy.reporting_month
         LEFT JOIN current_month AS curr_month
                   ON rev.reporting_month = curr_month.first_of_month
    WHERE rev.reporting_month <= (SELECT first_of_month FROM current_month)

Columns

View Definition

Possibly Referenced Tables/Views