SQL Consulting it

Bookings Compare

Bookings Compare model for consulting it.

source-preserved

Blocks

dbt SQL LKML LookML

bookings_compare.sql · 50 lines

inferred

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

SELECT
  dv.emp_id AS employee_id,
  dv.first_nm AS first_name,
  dv.last_nm AS last_name,
  dv.proj_nm AS project_name,
  dv.proj_id AS project_id,
  COALESCE(time_entry_dt, d_datum) AS period,
  dv.d_datum AS booking_date,
  ur.time_entry_dt AS actual_hours_date,
  ur.hours AS actual_hours,
  dv.daily_projected_hours AS booking_hours,
  timesheet.status_cd AS timesheet_status_cd
 FROM v_utilization_report AS ur
 FULL OUTER JOIN v_bookings dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.time_entry_dt = dv.d_datum)
 LEFT JOIN
  (SELECT cal.datum, sheet.emp_id, status_cd
   FROM t_timesheet sheet, t_calendar cal
   WHERE cal.datum BETWEEN sheet.start_dt AND sheet.end_dt
   AND status_cd in ('A', 'S')
  ) AS timesheet
  ON (ur.emp_id = timesheet.emp_id)
  AND (ur.time_entry_dt = timesheet.datum)

# models/schema.yml
version: 2

models:
  - name: bookings_compare
    columns:
      - name: employee_id
      - name: first_name
      - name: last_name
      - name: project_name
      - name: project_id
      - name: period
      - name: booking_date
      - name: has_booking
      - name: actual_hours_date
      - name: has_actual
      - name: actual_hours
      - name: timesheet_status
      - 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.sql · 25 lines

source-preserved validation: parses

SELECT
  dv.emp_id AS employee_id,
  dv.first_nm AS first_name,
  dv.last_nm AS last_name,
  dv.proj_nm AS project_name,
  dv.proj_id AS project_id,
  COALESCE(time_entry_dt, d_datum) AS period,
  dv.d_datum AS booking_date,
  ur.time_entry_dt AS actual_hours_date,
  ur.hours AS actual_hours,
  dv.daily_projected_hours AS booking_hours,
  timesheet.status_cd AS timesheet_status_cd
 FROM v_utilization_report AS ur
 FULL OUTER JOIN v_bookings dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.time_entry_dt = dv.d_datum)
 LEFT JOIN
  (SELECT cal.datum, sheet.emp_id, status_cd
   FROM t_timesheet sheet, t_calendar cal
   WHERE cal.datum BETWEEN sheet.start_dt AND sheet.end_dt
   AND status_cd in ('A', 'S')
  ) AS timesheet
  ON (ur.emp_id = timesheet.emp_id)
  AND (ur.time_entry_dt = timesheet.datum)

bookings_compare.view.lkml · 126 lines

inferred

view: bookings_compare {
  derived_table: {
    sql: SELECT
  dv.emp_id AS employee_id,
  dv.first_nm AS first_name,
  dv.last_nm AS last_name,
  dv.proj_nm AS project_name,
  dv.proj_id AS project_id,
  COALESCE(time_entry_dt, d_datum) AS period,
  dv.d_datum AS booking_date,
  ur.time_entry_dt AS actual_hours_date,
  ur.hours AS actual_hours,
  dv.daily_projected_hours AS booking_hours,
  timesheet.status_cd AS timesheet_status_cd
 FROM v_utilization_report AS ur
 FULL OUTER JOIN v_bookings dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.time_entry_dt = dv.d_datum)
 LEFT JOIN
  (SELECT cal.datum, sheet.emp_id, status_cd
   FROM t_timesheet sheet, t_calendar cal
   WHERE cal.datum BETWEEN sheet.start_dt AND sheet.end_dt
   AND status_cd in ('A', 'S')
  ) AS timesheet
  ON (ur.emp_id = timesheet.emp_id)
  AND (ur.time_entry_dt = timesheet.datum) ;;
  }

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

  dimension: first_name {
    sql: ${TABLE}.first_name ;;
  }

  dimension: last_name {
    sql: ${TABLE}.last_name ;;
  }

  dimension: project_name {
    sql: ${TABLE}.project_name ;;
  }

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

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

  dimension: booking_date {
    type: date
    sql: ${TABLE}.booking_date ;;
  }

  dimension: has_booking {
    type: yesno
    sql: ${booking_date} IS NOT NULL ;;
  }

  dimension: actual_hours_date {
    type: date
    sql: ${TABLE}.actual_hours_date ;;
  }

  dimension: has_actual {
    type: yesno
    sql: ${actual_hours_date} IS NOT NULL ;;
  }

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

  dimension: timesheet_status {
    sql: ${TABLE}.timesheet_status_cd ;;
  }

  dimension: plugged_hours {
    hidden: yes
    sql: (CASE WHEN ${timesheet_status} = 'A' THEN ${TABLE}.actual_hours ELSE ${TABLE}.booking_hours END) ;;
  }

  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.view.lookml · 85 lines

source-preserved

- view: bookings_compare
  derived_table:
    sql: SELECT
  dv.emp_id AS employee_id,
  dv.first_nm AS first_name,
  dv.last_nm AS last_name,
  dv.proj_nm AS project_name,
  dv.proj_id AS project_id,
  COALESCE(time_entry_dt, d_datum) AS period,
  dv.d_datum AS booking_date,
  ur.time_entry_dt AS actual_hours_date,
  ur.hours AS actual_hours,
  dv.daily_projected_hours AS booking_hours,
  timesheet.status_cd AS timesheet_status_cd
 FROM v_utilization_report AS ur
 FULL OUTER JOIN v_bookings dv
 ON (ur.emp_id = dv.emp_id)
 AND (ur.proj_id = dv.proj_id)
 AND (ur.time_entry_dt = dv.d_datum)
 LEFT JOIN
  (SELECT cal.datum, sheet.emp_id, status_cd
   FROM t_timesheet sheet, t_calendar cal
   WHERE cal.datum BETWEEN sheet.start_dt AND sheet.end_dt
   AND status_cd in ('A', 'S')
  ) AS timesheet
  ON (ur.emp_id = timesheet.emp_id)
  AND (ur.time_entry_dt = timesheet.datum)
  fields:
  - dimension: employee_id
    type: int
    sql: ${TABLE}.employee_id
  - dimension: first_name
    sql: ${TABLE}.first_name
  - dimension: last_name
    sql: ${TABLE}.last_name
  - dimension: project_name
    sql: ${TABLE}.project_name
  - dimension: project_id
    type: int
    sql: ${TABLE}.project_id
  - dimension: period
    type: time
    sql: ${TABLE}.period
  - dimension: booking_date
    type: date
    sql: ${TABLE}.booking_date
  - dimension: has_booking
    type: yesno
    sql: ${booking_date} IS NOT NULL
  - dimension: actual_hours_date
    type: date
    sql: ${TABLE}.actual_hours_date
  - dimension: has_actual
    type: yesno
    sql: ${actual_hours_date} IS NOT NULL
  - dimension: actual_hours
    hidden: true
    sql: COALESCE(${TABLE}.actual_hours, 0.0)
  - dimension: timesheet_status
    sql: ${TABLE}.timesheet_status_cd
  - dimension: plugged_hours
    hidden: true
    sql: (CASE WHEN ${timesheet_status} = 'A' THEN ${TABLE}.actual_hours ELSE ${TABLE}.booking_hours END)
  - 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.sql
bookings_compare.sql
bookings_compare.view.lkml
bookings_compare.view.lookml

Bookings Compare

Blocks

Assumptions

Contents