SQL Data analytics

Vehicle Daily View

Vehicle Daily View model for data analytics.

source-preserved

Blocks

dbt SQL LKML LookML

vehicle_daily_view.sql · 49 lines

inferred

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

SELECT a.dayofyear, a.year, a.month, v.usersvehicleid, a.available, v.name, CAST(a.mappingdate AS DATE) AS mappingdate, EXTRACT(DAYOFWEEK FROM CAST(a.mappingdate AS DATE)) AS dayname, e.enginehours, f.groupid
FROM
(SELECT vd.usersvehicleid, v.name, (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end) AS vehicleid FROM
(SELECT usersvehicleid, COUNT(*) AS tot FROM vehicle_tbl GROUP BY usersvehicleid) vd LEFT JOIN
(SELECT v.usersvehicleid, v.name, v.vehicleid, v.devicecode FROM vehicle_tbl v, device d WHERE d.deviceid=v.name and d.lasttotalconnecttime>0) v ON v.usersvehicleid=vd.usersvehicleid WHERE (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end)>0) v
INNER JOIN workorder_avail a ON v.usersvehicleid=a.unit_number LEFT JOIN
(SELECT
      v.vehicleid AS deviceid,
      SUM(enginehours) AS enginehours,
      EXTRACT(DAYOFYEAR FROM DATE(CONVERT_TIMEZONE('GMT','CST',dateadd(s,e.occurencedate,'1970-01-01')))) AS dayofy
      FROM
      engine_hours_tbl m,
      vehicle_tbl v,
      event_tbl e
      WHERE
      m.eventid=e.eventid and
      v.vehicleid=e.vehicleid and
      e.occurencedate < (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',current_date))) and
      e.occurencedate > (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',dateadd(d,-7usage_threshold_miles,current_date))))
      GROUP BY deviceid, dayofy) e ON  e.deviceid=v.vehicleid and a.dayofyear=e.dayofy LEFT JOIN {{ ref('device_firstinstalled') }} AS f ON f.deviceid=v.vehicleid
WHERE CAST(a.mappingdate AS DATE) > f.mindate and a.dayofyear <> 133
ORDER BY mappingdate, usersvehicleid

# models/schema.yml
version: 2

models:
  - name: vehicle_daily_view
    columns:
      - name: dayofyear
      - name: year
      - name: month
      - name: usersvehicleid
        description: "Your Company Listed Unit Number"
      - name: available
      - name: name
      - name: groupid
      - name: mappingdate
      - name: dayname
      - name: enginehours
      - name: weekend
      - name: units_available
      - name: dayused
      - name: daily_fleet
      - name: daily_fleet_avail
      - name: daily_fleet_used

Assumptions

Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
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.

vehicle_daily_view.sql · 22 lines

source-preserved validation: not checked

SELECT a.dayofyear, a.year, a.month, v.usersvehicleid, a.available, v.name, CAST(a.mappingdate AS DATE) AS mappingdate, EXTRACT(DAYOFWEEK FROM CAST(a.mappingdate AS DATE)) AS dayname, e.enginehours, f.groupid
FROM
(SELECT vd.usersvehicleid, v.name, (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end) AS vehicleid FROM
(SELECT usersvehicleid, COUNT(*) AS tot FROM vehicle_tbl GROUP BY usersvehicleid) vd LEFT JOIN
(SELECT v.usersvehicleid, v.name, v.vehicleid, v.devicecode FROM vehicle_tbl v, device d WHERE d.deviceid=v.name and d.lasttotalconnecttime>0) v ON v.usersvehicleid=vd.usersvehicleid WHERE (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end)>0) v
INNER JOIN workorder_avail a ON v.usersvehicleid=a.unit_number LEFT JOIN
(SELECT
      v.vehicleid AS deviceid,
      SUM(enginehours) AS enginehours,
      EXTRACT(DAYOFYEAR FROM DATE(CONVERT_TIMEZONE('GMT','CST',dateadd(s,e.occurencedate,'1970-01-01')))) AS dayofy
      FROM
      engine_hours_tbl m,
      vehicle_tbl v,
      event_tbl e
      WHERE
      m.eventid=e.eventid and
      v.vehicleid=e.vehicleid and
      e.occurencedate < (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',current_date))) and
      e.occurencedate > (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',dateadd(d,-7usage_threshold_miles,current_date))))
      GROUP BY deviceid, dayofy) e ON  e.deviceid=v.vehicleid and a.dayofyear=e.dayofy LEFT JOIN ${device_firstinstalled.SQL_TABLE_NAME} AS f ON f.deviceid=v.vehicleid
WHERE CAST(a.mappingdate AS DATE) > f.mindate and a.dayofyear <> 133
ORDER BY mappingdate, usersvehicleid

vehicle_daily_view.view.lkml · 150 lines

inferred

view: vehicle_daily_view {
  derived_table: {
    sql: SELECT a.dayofyear, a.year, a.month, v.usersvehicleid, a.available, v.name, CAST(a.mappingdate AS DATE) AS mappingdate, EXTRACT(DAYOFWEEK FROM CAST(a.mappingdate AS DATE)) AS dayname, e.enginehours, f.groupid
FROM
(SELECT vd.usersvehicleid, v.name, (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end) AS vehicleid FROM
(SELECT usersvehicleid, COUNT(*) AS tot FROM vehicle_tbl GROUP BY usersvehicleid) vd LEFT JOIN
(SELECT v.usersvehicleid, v.name, v.vehicleid, v.devicecode FROM vehicle_tbl v, device d WHERE d.deviceid=v.name and d.lasttotalconnecttime>0) v ON v.usersvehicleid=vd.usersvehicleid WHERE (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end)>0) v
INNER JOIN workorder_avail a ON v.usersvehicleid=a.unit_number LEFT JOIN
(SELECT
      v.vehicleid AS deviceid,
      SUM(enginehours) AS enginehours,
      EXTRACT(DAYOFYEAR FROM DATE(CONVERT_TIMEZONE('GMT','CST',dateadd(s,e.occurencedate,'1970-01-01')))) AS dayofy
      FROM
      engine_hours_tbl m,
      vehicle_tbl v,
      event_tbl e
      WHERE
      m.eventid=e.eventid and
      v.vehicleid=e.vehicleid and
      e.occurencedate < (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',current_date))) and
      e.occurencedate > (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',dateadd(d,-7usage_threshold_miles,current_date))))
      GROUP BY deviceid, dayofy) e ON  e.deviceid=v.vehicleid and a.dayofyear=e.dayofy LEFT JOIN ${device_firstinstalled.SQL_TABLE_NAME} AS f ON f.deviceid=v.vehicleid
WHERE CAST(a.mappingdate AS DATE) > f.mindate and a.dayofyear <> 133
ORDER BY mappingdate, usersvehicleid ;;
  }

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

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

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

  dimension: usersvehicleid {
    label: "Vehicle Unit Number"
    description: "Your Company Listed Unit Number"
    sql: ${TABLE}.usersvehicleid ;;
  }

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

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

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

  dimension: mappingdate {
    type: date_date
    sql: ${TABLE}.mappingdate ;;
  }

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

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

  dimension: weekend {
  }

  dimension: units_available {
  }

  dimension: dayused {
  }

  dimension: daily_fleet {
    sql: CASE
WHEN ${dayused} = 'Vehicle Used' THEN ${dayused}
WHEN ${dayused} = 'Vehicle Unused' THEN ${units_available}
ELSE ${units_available} END ;;
  }

  dimension: daily_fleet_avail {
    type: int
    sql: CASE
WHEN ${enginehours} > .usage_threshold_miles THEN 1
ELSE ${available} END ;;
  }

  dimension: daily_fleet_used {
    type: int
    sql: CASE
WHEN ${enginehours} > .usage_threshold_miles THEN 1
ELSE 0 END ;;
  }

  measure: count {
    type: count
    drill_fields: [usersvehicleid, mappingdate, units_available, dayused, enginehours]
  }

  measure: enginesum {
    type: sum
    value_format: "##.##"
    drill_fields: [usersvehicleid, mappingdate, units_available, dayused, enginehours]
    sql: ${enginehours} ;;
  }

  measure: availablesum {
    type: sum
    value_format: "#,##0"
    drill_fields: [usersvehicleid, mappingdate, units_available, dayused, enginehours]
    sql: ${daily_fleet_avail} ;;
  }

  measure: usedsum {
    type: sum
    value_format: "#,##0"
    drill_fields: [usersvehicleid, mappingdate, units_available, dayused, enginehours]
    sql: ${daily_fleet_used} ;;
  }

  measure: countdays {
    type: count_distinct
    sql: ${dayofyear} ;;
  }

  measure: vehicle_engine_hours_per_available_day {
    type: number
    sql: ROUND(${enginesum}/NULLIF(${availablesum},0),2) ;;
  }

  measure: vehicle_availability {
    type: number
    sql: ROUND(100.0*${availablesum}/NULLIF(${count},0),2) ;;
  }

  measure: vehicle_usage {
    type: number
    sql: ROUND(100.0*${usedsum}/NULLIF(${count},0),2) ;;
  }
}

vehicle_daily_view.view.lookml · 121 lines

source-preserved

- view: vehicle_daily_view
  derived_table:
    sql: SELECT a.dayofyear, a.year, a.month, v.usersvehicleid, a.available, v.name, CAST(a.mappingdate AS DATE) AS mappingdate, EXTRACT(DAYOFWEEK FROM CAST(a.mappingdate AS DATE)) AS dayname, e.enginehours, f.groupid
FROM
(SELECT vd.usersvehicleid, v.name, (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end) AS vehicleid FROM
(SELECT usersvehicleid, COUNT(*) AS tot FROM vehicle_tbl GROUP BY usersvehicleid) vd LEFT JOIN
(SELECT v.usersvehicleid, v.name, v.vehicleid, v.devicecode FROM vehicle_tbl v, device d WHERE d.deviceid=v.name and d.lasttotalconnecttime>0) v ON v.usersvehicleid=vd.usersvehicleid WHERE (case when vd.tot>1 then (case when devicecode LIKE 'A%' then 0 else v.vehicleid end) else v.vehicleid end)>0) v
INNER JOIN workorder_avail a ON v.usersvehicleid=a.unit_number LEFT JOIN
(SELECT
      v.vehicleid AS deviceid,
      SUM(enginehours) AS enginehours,
      EXTRACT(DAYOFYEAR FROM DATE(CONVERT_TIMEZONE('GMT','CST',dateadd(s,e.occurencedate,'1970-01-01')))) AS dayofy
      FROM
      engine_hours_tbl m,
      vehicle_tbl v,
      event_tbl e
      WHERE
      m.eventid=e.eventid and
      v.vehicleid=e.vehicleid and
      e.occurencedate < (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',current_date))) and
      e.occurencedate > (SELECT extract(EPOCH FROM CONVERT_TIMEZONE('US/Central','GMT',dateadd(d,-7usage_threshold_miles,current_date))))
      GROUP BY deviceid, dayofy) e ON  e.deviceid=v.vehicleid and a.dayofyear=e.dayofy LEFT JOIN ${device_firstinstalled.SQL_TABLE_NAME} AS f ON f.deviceid=v.vehicleid
WHERE CAST(a.mappingdate AS DATE) > f.mindate and a.dayofyear <> 133
ORDER BY mappingdate, usersvehicleid
  fields:
  - dimension: dayofyear
    type: number
    sql: ${TABLE}.dayofyear
  - dimension: year
    type: number
    sql: ${TABLE}.year
  - dimension: month
    type: number
    sql: ${TABLE}.month
  - dimension: usersvehicleid
    label: Vehicle Unit Number
    description: Your Company Listed Unit Number
    sql: ${TABLE}.usersvehicleid
  - dimension: available
    type: number
    sql: ${TABLE}.available
  - dimension: name
    sql: ${TABLE}.name
  - dimension: groupid
    sql: ${TABLE}.groupid
  - dimension: mappingdate
    type: date_date
    sql: ${TABLE}.mappingdate
  - dimension: dayname
    type: number
    sql: ${TABLE}.dayname
  - dimension: enginehours
    sql: ${TABLE}.enginehours
  - dimension: weekend
  - dimension: units_available
  - dimension: dayused
  - dimension: daily_fleet
    sql: CASE
WHEN ${dayused} = 'Vehicle Used' THEN ${dayused}
WHEN ${dayused} = 'Vehicle Unused' THEN ${units_available}
ELSE ${units_available} END
  - dimension: daily_fleet_avail
    type: int
    sql: CASE
WHEN ${enginehours} > .usage_threshold_miles THEN 1
ELSE ${available} END
  - dimension: daily_fleet_used
    type: int
    sql: CASE
WHEN ${enginehours} > .usage_threshold_miles THEN 1
ELSE 0 END
  - measure: count
    type: count
    drill_fields:
    - usersvehicleid
    - mappingdate
    - units_available
    - dayused
    - enginehours
  - measure: enginesum
    type: sum
    value_format: '##.##'
    drill_fields:
    - usersvehicleid
    - mappingdate
    - units_available
    - dayused
    - enginehours
    sql: ${enginehours}
  - measure: availablesum
    type: sum
    value_format: '#,##0'
    drill_fields:
    - usersvehicleid
    - mappingdate
    - units_available
    - dayused
    - enginehours
    sql: ${daily_fleet_avail}
  - measure: usedsum
    type: sum
    value_format: '#,##0'
    drill_fields:
    - usersvehicleid
    - mappingdate
    - units_available
    - dayused
    - enginehours
    sql: ${daily_fleet_used}
  - measure: countdays
    type: count_distinct
    sql: ${dayofyear}
  - measure: vehicle_engine_hours_per_available_day
    type: number
    sql: ROUND(${enginesum}/NULLIF(${availablesum},0),2)
  - measure: vehicle_availability
    type: number
    sql: ROUND(100.0*${availablesum}/NULLIF(${count},0),2)
  - measure: vehicle_usage
    type: number
    sql: ROUND(100.0*${usedsum}/NULLIF(${count},0),2)

vehicle_daily_view.sql
vehicle_daily_view.sql
vehicle_daily_view.view.lkml
vehicle_daily_view.view.lookml

Vehicle Daily View

Blocks

Assumptions

Contents