VW_HourlyLaborSummary_2

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

report_month

date

√

null

kra_workdate

datetime2

16,3

null

parent_facility_code

varchar

null

pay_type

varchar

null

job_code

varchar

null

job

varchar

100

null

total_hours

decimal

40,2

√

null

total_wages

decimal

40,6

√

null

total_daily_census

int

√

null

View Definition

CREATE VIEW dbo.VW_HourlyLaborSummary_2 AS

/*********************************************************************
  GMLI-related supplemental view to provide additional insight into
  hourly labor.

  NOTE: Since Kronos does not track the specific facility type
        (SNF, etc), this data is at a lower level of resolution
        than e.g. the PCC census data. That implies duplication
        for "buildings" that have more than facility type.
*********************************************************************/

    WITH all_wages AS (
        SELECT kra_workdate,
               ReportAcctFacCode AS parent_facility_code,
               CASE WHEN job_code IN ('EMS001') THEN 'C19Hours' ELSE pay_type END AS pay_type,
               job_code,
               job,
               total_hours,
               total_wages,
               total_daily_census
        FROM dbo.Kronos_HourlyWages
        UNION ALL
        SELECT kra_workdate,
               ReportAcctFacCode AS parent_facility_code,
               'C19Hours' AS pay_type,
               job_code,
               job,
               total_hours,
               total_wages,
               CAST(0 AS INT) AS total_daily_census
        FROM dbo.Kronos_C19HourlyWages
        UNION ALL
        SELECT md.KRAWorkDate AS kra_workdate,
               facmem.ReportAcctFacCode AS parent_facility_code,
               CASE WHEN PayCodeName = '$ CHLD' THEN 'C19ChildCare' ELSE 'C19HazardPay' END AS pay_type,
               md.JobCode AS job_code,
               md.Job AS job,
               CAST(0 AS DECIMAL(38, 6)) AS total_hours,
               md.TotalMoneyAmount AS total_wages,
               CAST(0 AS INT) AS total_daily_census
        FROM dbo.Kronos_C19MoneyDaily AS md
        JOIN dbo.AutoPop_AvaReportingFacMembers AS facmem
            ON md.AcctFacCode = facmem.AcctFacCode
    )
    SELECT DATEFROMPARTS(YEAR(kra_workdate), MONTH(kra_workdate), 1) AS report_month,
           kra_workdate,
           parent_facility_code,
           pay_type,
           job_code,
           job,
           SUM(total_hours) AS total_hours,
           SUM(total_wages) AS total_wages,
           SUM(total_daily_census) AS total_daily_census
    FROM all_wages
    GROUP BY kra_workdate,
             parent_facility_code,
             pay_type,
             job_code,
             job

Columns

View Definition

Possibly Referenced Tables/Views