VW_GMLI_CBC

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

facility_code

varchar

null

facility_name

varchar

300

null

division

varchar

null

facility_type

varchar

254

√

null

reporting_month

date

√

null

reporting_day

date

√

null

total_census_days

int

√

null

total_census_bedhold_days

int

√

null

all_hourly_wages

decimal

40,6

√

null

overtime_hourly_wages

decimal

40,6

√

null

View Definition

CREATE VIEW dbo.VW_GMLI_CBC AS

/*********************************************************************
  Census and labor costs for CBC's. "GMLI Light" requested by Rich.

  To keep things simple, as-of date "freshness" indication is
  handled in a separate view.

  IMPORTANT:  Cannot subtotal by e.g. payor because labor costs
              are not tracked by payor.
*********************************************************************/

    WITH wages AS (
        SELECT kra_workdate,
               ReportAcctFacCode AS facility_code,
               pay_type,
               total_wages,
               DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(kra_workdate))) AS reporting_month
        FROM dbo.Kronos_HourlyWages
        WHERE kra_workdate < CONVERT(DATE, GETDATE())
    ),
         wages_sum AS (
             SELECT facility_code,
                    reporting_month,
                    kra_workdate,
                    sum(CASE WHEN pay_type = 'Overtime' THEN total_wages ELSE 0 END) AS overtime_wages,
                    sum(total_wages)                                                 AS total_wages
             FROM wages
             GROUP BY facility_code,
                      reporting_month,
                      kra_workdate
         ),
         census AS (
             SELECT fac.ReportAcctFacCode,
                    faccache.FacilityType,
                    DATEADD(MM, -1, DATEADD(DD, 1, EOMONTH(CONVERT(DATE, reimb.CensusDate)))) AS reporting_month,
                    reimb.CensusDate,
                    reimb.CensusStatusCode
             FROM dbo.PCC_Census_Reimb AS reimb
             JOIN dbo.PCC_Cache_FacID AS faccache
                  ON reimb.FacilityID = faccache.FacilityID
             JOIN dbo.AutoPop_AvaReportingFacMembers AS facmem
                  ON reimb.FacilityCode = facmem.AcctFacCode
             JOIN dbo.AutoPop_AvaReportingFac AS fac
                  ON facmem.ReportAcctFacCode = fac.ReportAcctFacCode
             WHERE reimb.CensusDate < CONVERT(DATE, GETDATE())
         ),
         census_sum AS (
             SELECT ReportAcctFacCode,
                    FacilityType,
                    reporting_month,
                    CensusDate,
                    COUNT(*) AS total_census_days,
                    SUM(CASE WHEN census.CensusStatusCode = 'A' THEN 0 ELSE 1 END) AS total_census_bedhold_days
             FROM census
             WHERE CensusDate < CONVERT(DATE, GETDATE())
             GROUP BY ReportAcctFacCode,
                      FacilityType,
                      reporting_month,
                      CensusDate
         )

    SELECT fac.ReportAcctFacCode                AS facility_code,
           fac.DisplayName                      AS facility_name,
           fac.division,
           census_sum.FacilityType              AS facility_type,
           census_sum.reporting_month,
           CONVERT(DATE, census_sum.CensusDate) AS reporting_day,
           census_sum.total_census_days,
           census_sum.total_census_bedhold_days,
           wages_sum.total_wages                AS all_hourly_wages,
           wages_sum.overtime_wages             AS overtime_hourly_wages
    FROM dbo.AutoPop_AvaReportingFac AS fac --Driver table
    LEFT JOIN census_sum --Auxiliary driver table. LEFT JOINs are to ensure all facilities represented even if there's missing data
              ON fac.ReportAcctFacCode = census_sum.ReportAcctFacCode
    LEFT JOIN wages_sum
              ON census_sum.ReportAcctFacCode = wages_sum.facility_code
                  AND census_sum.reporting_month = wages_sum.reporting_month
                  AND census_sum.CensusDate = wages_sum.kra_workdate
    WHERE fac.Division = 'CBC'
      AND fac.IsActive = 'Y'

Columns

View Definition

Possibly Referenced Tables/Views