Payroll Check Weekly

-- models/payroll_check_weekly.sql
{{ config(materialized='table') }}

SELECT
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    SUM(f.lineamount) AS amount,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours
FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY f.department_key, d.IsoWeek, d.IsoYear

# models/schema.yml
version: 2

models:
  - name: payroll_check_weekly
    columns:
      - name: payroll_dept_key
        tests:
          - unique
          - not_null
      - name: iso_week
      - name: iso_year
      - name: amount
      - name: hours

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    SUM(f.lineamount) AS amount,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours
FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY f.department_key, d.IsoWeek, d.IsoYear

view: payroll_check_weekly {
  derived_table: {
    sql: SELECT
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    SUM(f.lineamount) AS amount,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours
FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY f.department_key, d.IsoWeek, d.IsoYear ;;
    persist_for: "24 hours"
  }

  dimension: payroll_dept_key {
    type: int
    primary_key: yes
    sql: ${TABLE}.payroll_dept_key ;;
  }

  dimension: iso_week {
    type: int
    sql: ${TABLE}.iso_week ;;
  }

  dimension: iso_year {
    type: int
    sql: ${TABLE}.iso_year ;;
  }

  dimension: amount {
    type: number
    sql: ${TABLE}.amount ;;
  }

  dimension: hours {
    type: number
    sql: ${TABLE}.hours ;;
  }

  measure: total_check_amount {
    type: sum
    sql: ${amount} ;;
  }

  measure: total_hours {
    type: sum
    sql: ${hours} ;;
  }

  measure: total_ot_hours {
    type: sum
    sql: ${TABLE}.ot_hours ;;
  }

  measure: total_vacation {
    type: sum
    sql: ${TABLE}.vaca_amount ;;
  }

  measure: labor_as_perc_of_sales {
    type: number
    value_format: "0.00%"
    sql: (${total_check_amount} / NULLIF(${sales_weekly.total_gross_amount},0)) ;;
  }
}

- view: payroll_check_weekly
  derived_table:
    sql: SELECT
    f.department_key AS payroll_dept_key,
    d.IsoWeek AS iso_week,
    d.IsoYear AS iso_year,
    SUM(f.lineamount) AS amount,
    SUM(CASE WHEN isOT = 'Y' THEN linehours ELSE 0.0 END) AS ot_hours,
    SUM(CASE WHEN e.BkEarningsCode = 'VAC' THEN lineamount ELSE 0.0 END) AS vaca_amount,
    SUM(f.linehours) AS hours
FROM FactPayrollCheck f
INNER JOIN DimDate d
ON f.CheckDateKey = d.DateKey
INNER JOIN DimEarningsCode e
ON f.EarningsCodeKey = e.EarningsCodeKey
WHERE isEmployerCost = 'Y'
GROUP BY f.department_key, d.IsoWeek, d.IsoYear
    persist_for: 24 hours
  fields:
  - dimension: payroll_dept_key
    type: int
    primary_key: true
    sql: ${TABLE}.payroll_dept_key
  - dimension: iso_week
    type: int
    sql: ${TABLE}.iso_week
  - dimension: iso_year
    type: int
    sql: ${TABLE}.iso_year
  - dimension: amount
    type: number
    sql: ${TABLE}.amount
  - dimension: hours
    type: number
    sql: ${TABLE}.hours
  - measure: total_check_amount
    type: sum
    sql: ${amount}
  - measure: total_hours
    type: sum
    sql: ${hours}
  - measure: total_ot_hours
    type: sum
    sql: ${TABLE}.ot_hours
  - measure: total_vacation
    type: sum
    sql: ${TABLE}.vaca_amount
  - measure: labor_as_perc_of_sales
    type: number
    value_format: '0.00%'
    sql: (${total_check_amount} / NULLIF(${sales_weekly.total_gross_amount},0))