dbt Consulting it

Bookings Compare Weekly

Bookings Compare Weekly model for consulting it.

inferred

Blocks

dbt SQL LKML LookML

bookings_compare_weekly.sql · 48 lines

inferred

-- models/bookings_compare_weekly.sql
{{ config(materialized='view') }}

SELECT
  COALESCE(dv.emp_id, ur.emp_id) AS employee_id,
  COALESCE(dv.proj_id, ur.proj_id) AS project_id,
  COALESCE(ur.year, dv.year) AS periodYear,
  COALESCE(ur.calendarweek, dv.calendarweek) AS periodWeekNum,
  dv.calendarweek AS booking_weeknum,
  ur.calendarweek AS actual_weeknum,
  ur.hours AS actual_hours,
  dv.sumOfBookings AS booking_hours,
  cal.datum AS mondayDate
 FROM
    (SELECT util.emp_id, util.proj_id, util.first_nm, util.last_nm, util.proj_nm, cal.year, cal.calendarweek, SUM(hours) AS hours
    FROM v_utilization_report util, t_calendar cal
    WHERE cal.datum = util.time_entry_dt
    GROUP BY emp_id, proj_id, first_nm, last_nm, proj_nm, cal.year, cal.calendarweek
    ) AS ur
 FULL OUTER JOIN
    (SELECT bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek, SUM(bookings.daily_projected_hours) AS sumOfBookings
    FROM v_bookings bookings INNER JOIN t_calendar cal
    ON (cal.datum = bookings.d_datum)
    GROUP BY bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek
    ) AS dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.year = dv.year)
 AND (ur.calendarweek = dv.calendarweek)
 INNER JOIN (SELECT datum, year, calendarweek FROM t_calendar WHERE weekdayname = 'Monday') cal ON (COALESCE(ur.year, dv.year) = cal.year) AND (COALESCE(ur.calendarweek, dv.calendarweek) = cal.calendarweek)

# models/schema.yml
version: 2

models:
  - name: bookings_compare_weekly
    columns:
      - name: employee_id
      - name: project_id
      - name: periodYear
      - name: periodWeekNum
      - name: mondayDate
      - name: has_booking
      - name: has_actual
      - name: actual_hours
      - name: plugged_hours
      - name: has_variance
      - name: booking_hours

Assumptions

materialized='view' is the default; no persist hint found.
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

bookings_compare_weekly.sql · 27 lines

source-preserved validation: parses

SELECT
  COALESCE(dv.emp_id, ur.emp_id) AS employee_id,
  COALESCE(dv.proj_id, ur.proj_id) AS project_id,
  COALESCE(ur.year, dv.year) AS periodYear,
  COALESCE(ur.calendarweek, dv.calendarweek) AS periodWeekNum,
  dv.calendarweek AS booking_weeknum,
  ur.calendarweek AS actual_weeknum,
  ur.hours AS actual_hours,
  dv.sumOfBookings AS booking_hours,
  cal.datum AS mondayDate
 FROM
    (SELECT util.emp_id, util.proj_id, util.first_nm, util.last_nm, util.proj_nm, cal.year, cal.calendarweek, SUM(hours) AS hours
    FROM v_utilization_report util, t_calendar cal
    WHERE cal.datum = util.time_entry_dt
    GROUP BY emp_id, proj_id, first_nm, last_nm, proj_nm, cal.year, cal.calendarweek
    ) AS ur
 FULL OUTER JOIN
    (SELECT bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek, SUM(bookings.daily_projected_hours) AS sumOfBookings
    FROM v_bookings bookings INNER JOIN t_calendar cal
    ON (cal.datum = bookings.d_datum)
    GROUP BY bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek
    ) AS dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.year = dv.year)
 AND (ur.calendarweek = dv.calendarweek)
 INNER JOIN (SELECT datum, year, calendarweek FROM t_calendar WHERE weekdayname = 'Monday') cal ON (COALESCE(ur.year, dv.year) = cal.year) AND (COALESCE(ur.calendarweek, dv.calendarweek) = cal.calendarweek)

bookings_compare_weekly.view.lkml · 112 lines

inferred

view: bookings_compare_weekly {
  derived_table: {
    sql: SELECT
  COALESCE(dv.emp_id, ur.emp_id) AS employee_id,
  COALESCE(dv.proj_id, ur.proj_id) AS project_id,
  COALESCE(ur.year, dv.year) AS periodYear,
  COALESCE(ur.calendarweek, dv.calendarweek) AS periodWeekNum,
  dv.calendarweek AS booking_weeknum,
  ur.calendarweek AS actual_weeknum,
  ur.hours AS actual_hours,
  dv.sumOfBookings AS booking_hours,
  cal.datum AS mondayDate
 FROM
    (SELECT util.emp_id, util.proj_id, util.first_nm, util.last_nm, util.proj_nm, cal.year, cal.calendarweek, SUM(hours) AS hours
    FROM v_utilization_report util, t_calendar cal
    WHERE cal.datum = util.time_entry_dt
    GROUP BY emp_id, proj_id, first_nm, last_nm, proj_nm, cal.year, cal.calendarweek
    ) AS ur
 FULL OUTER JOIN
    (SELECT bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek, SUM(bookings.daily_projected_hours) AS sumOfBookings
    FROM v_bookings bookings INNER JOIN t_calendar cal
    ON (cal.datum = bookings.d_datum)
    GROUP BY bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek
    ) AS dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.year = dv.year)
 AND (ur.calendarweek = dv.calendarweek)
 INNER JOIN (SELECT datum, year, calendarweek FROM t_calendar WHERE weekdayname = 'Monday') cal ON (COALESCE(ur.year, dv.year) = cal.year) AND (COALESCE(ur.calendarweek, dv.calendarweek) = cal.calendarweek) ;;
  }

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

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

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

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

  dimension: mondayDate {
    type: time
    sql: ${TABLE}.mondayDate ;;
  }

  dimension: has_booking {
    type: yesno
    sql: ${TABLE}.booking_weeknum IS NOT NULL ;;
  }

  dimension: has_actual {
    type: yesno
    sql: ${TABLE}.actual_weeknum IS NOT NULL ;;
  }

  dimension: actual_hours {
    hidden: yes
    sql: COALESCE(${TABLE}.actual_hours, 0.0) ;;
  }

  dimension: plugged_hours {
    hidden: yes
    sql: COALESCE(${TABLE}.actual_hours, ${TABLE}.booking_hours, 0.0) ;;
  }

  dimension: has_variance {
    type: yesno
    sql: ${booking_hours} - ${actual_hours} <> 0 ;;
  }

  dimension: booking_hours {
    type: number
    hidden: yes
    sql: COALESCE(${TABLE}.booking_hours, 0.0) ;;
  }

  measure: total_booking_hours {
    type: sum
    sql: ${booking_hours} ;;
  }

  measure: total_actual_hours {
    type: sum
    sql: ${actual_hours} ;;
  }

  measure: total_plugged_hours {
    type: sum
    sql: ${plugged_hours} ;;
  }

  measure: total_variance {
    type: number
    sql: ${total_booking_hours} - ${total_actual_hours} ;;
  }

  measure: percent_variance {
    type: number
    sql: 100.0 * ${total_variance} / ${total_booking_hours} ;;
  }
}

bookings_compare_weekly.view.lookml · 79 lines

source-preserved

- view: bookings_compare_weekly
  derived_table:
    sql: SELECT
  COALESCE(dv.emp_id, ur.emp_id) AS employee_id,
  COALESCE(dv.proj_id, ur.proj_id) AS project_id,
  COALESCE(ur.year, dv.year) AS periodYear,
  COALESCE(ur.calendarweek, dv.calendarweek) AS periodWeekNum,
  dv.calendarweek AS booking_weeknum,
  ur.calendarweek AS actual_weeknum,
  ur.hours AS actual_hours,
  dv.sumOfBookings AS booking_hours,
  cal.datum AS mondayDate
 FROM
    (SELECT util.emp_id, util.proj_id, util.first_nm, util.last_nm, util.proj_nm, cal.year, cal.calendarweek, SUM(hours) AS hours
    FROM v_utilization_report util, t_calendar cal
    WHERE cal.datum = util.time_entry_dt
    GROUP BY emp_id, proj_id, first_nm, last_nm, proj_nm, cal.year, cal.calendarweek
    ) AS ur
 FULL OUTER JOIN
    (SELECT bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek, SUM(bookings.daily_projected_hours) AS sumOfBookings
    FROM v_bookings bookings INNER JOIN t_calendar cal
    ON (cal.datum = bookings.d_datum)
    GROUP BY bookings.emp_id, bookings.proj_id, cal.year, cal.calendarweek
    ) AS dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.year = dv.year)
 AND (ur.calendarweek = dv.calendarweek)
 INNER JOIN (SELECT datum, year, calendarweek FROM t_calendar WHERE weekdayname = 'Monday') cal ON (COALESCE(ur.year, dv.year) = cal.year) AND (COALESCE(ur.calendarweek, dv.calendarweek) = cal.calendarweek)
  fields:
  - dimension: employee_id
    type: int
    sql: ${TABLE}.employee_id
  - dimension: project_id
    type: int
    sql: ${TABLE}.project_id
  - dimension: periodYear
    type: int
    sql: ${TABLE}.periodYear
  - dimension: periodWeekNum
    type: int
    sql: ${TABLE}.periodWeekNum
  - dimension: mondayDate
    type: time
    sql: ${TABLE}.mondayDate
  - dimension: has_booking
    type: yesno
    sql: ${TABLE}.booking_weeknum IS NOT NULL
  - dimension: has_actual
    type: yesno
    sql: ${TABLE}.actual_weeknum IS NOT NULL
  - dimension: actual_hours
    hidden: true
    sql: COALESCE(${TABLE}.actual_hours, 0.0)
  - dimension: plugged_hours
    hidden: true
    sql: COALESCE(${TABLE}.actual_hours, ${TABLE}.booking_hours, 0.0)
  - dimension: has_variance
    type: yesno
    sql: ${booking_hours} - ${actual_hours} <> 0
  - dimension: booking_hours
    type: number
    hidden: true
    sql: COALESCE(${TABLE}.booking_hours, 0.0)
  - measure: total_booking_hours
    type: sum
    sql: ${booking_hours}
  - measure: total_actual_hours
    type: sum
    sql: ${actual_hours}
  - measure: total_plugged_hours
    type: sum
    sql: ${plugged_hours}
  - measure: total_variance
    type: number
    sql: ${total_booking_hours} - ${total_actual_hours}
  - measure: percent_variance
    type: number
    sql: 100.0 * ${total_variance} / ${total_booking_hours}

bookings_compare_weekly.sql
bookings_compare_weekly.sql
bookings_compare_weekly.view.lkml
bookings_compare_weekly.view.lookml

Bookings Compare Weekly

Blocks

Assumptions

Contents