Daily Vehicle Summary

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

SELECT s.dailysummaryid, s.deviceidint, accountidint, displayname, daystart,dayend,timezone,datename,idletotal
,fueltotal,enginetotal,odometertotal,(CASE WHEN ptototal>idletotal THEN boomtotal ELSE ptototal END) AS ptototal,boomtotal,totalevents,accelerationtotal,brakingtotal
,corneringtotal,faulttotal,speedingtotal,maxspeed,lowbatterytotal,enginestarttotal,enginestoptotal,devicecode,firstcomm,accountid, EXTRACT(DAYOFWEEK FROM datename) AS dayname
FROM
  daily_summary s
  LEFT JOIN delta_summary d ON s.dailysummaryid=d.dailysummaryid
  LEFT JOIN event_summary e ON e.dailysummaryid=s.dailysummaryid
  LEFT JOIN (SELECT d.deviceidint, d.displayname, d.devicecode, d.accountid
    FROM device d) c ON c.deviceidint=s.deviceidint
  INNER JOIN (SELECT es.deviceidint, MIN(da.daystart) AS firstcomm FROM daily_summary da, event_summary es
    WHERE es.dailysummaryid=da.dailysummaryid and es.totalevents>0 GROUP BY es.deviceidint) ev ON ev.deviceidint=s.deviceidint
  INNER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid=l.accountid) l ON l.deviceidint=s.deviceidint
  FULL OUTER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid='dual_placeholder1') dual ON dual.deviceidint=s.deviceidint
where dual.deviceidint IS NULL

# models/schema.yml
version: 2

models:
  - name: daily_vehicle_summary
    columns:
      - name: dailysummaryid
      - name: deviceidint
      - name: accountidint
      - name: daystart
      - name: dayend
      - name: timezone
      - name: idletotal
      - name: idlehoursadjust
      - name: idlehourscalc
        description: "Unit Total Idle Hours By Day"
      - name: idlehoursnoboom
        description: "Unit Specific Idle Hours Without Boom By Day"
      - name: idlehoursnopto
        description: "Unit Specific Idle Hours Without PTO By Day"
      - name: fueltotal
        description: "Unit Specific Fuel Consumption By Day"
      - name: enginetotal
        description: "Unit Specific Engine Hours By Day"
      - name: enginehoursnoidle
        description: "Unit Specific Driving Hours By Day"
      - name: driving
        description: "Unit Driving Hours By Day"
      - name: odometertotal
        description: "Unit Specific Mileage By Day"
      - name: daily_fleet_used
        description: "Unit Specific Usage By Day - Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven"
      - name: ptototal
        description: "Unit Specific PTO Hours By Day"
      - name: ptonoboomtotal
        description: "Unit Specific PTO Hours Without Boom Hours By Day"
      - name: boomtotal
        description: "Unit Specific Boom Hours By Day"
      - name: totalevents
      - name: accelerationtotal
        description: "Unit Specific Acceleration Events By Day - Company default settings is an event when driver accelerates greater than acceleration_event_threshold"
      - name: brakingtotal
        description: "Unit Specific Braking Events By Day - Company default settings is an event when driver accelerates greater than braking_event_threshold"
      - name: corneringtotal
        description: "Unit Specific Cornering Events By Day - Company default settings is an event when the vehicle\u2019s centrifugal g-force is over the specific G-force threshold of 7usage_threshold_miles0mg for .1 seconds"
      - name: faulttotal
        description: "Unit Specific Fault Code Events By Day"
      - name: speedingtotal
        description: "Unit Specific Speeding Events By Day - Company default setting is an event exceeding 77mph for usage_threshold_miles seconds.  This event turns off when the vehicle goes below 7usage_threshold_miles mph for usage_threshold_miles seconds"
      - name: maxspeed
        description: "Maximum Speed By Vehicle By Day"
      - name: lowbatterytotal
        description: "Unit Specific Low Battery Events By Day"
      - name: enginestarttotal
      - name: enginestoptotal
        description: "Unit Specific Engine Stops By Day"
      - name: usersvehicleid
        description: "Unit Number From Company Platform"
      - name: devicecode
      - name: accountid
      - name: firstcomm
      - name: dayname
      - name: gooddate
      - name: weekend

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.

SELECT s.dailysummaryid, s.deviceidint, accountidint, displayname, daystart,dayend,timezone,datename,idletotal
,fueltotal,enginetotal,odometertotal,(CASE WHEN ptototal>idletotal THEN boomtotal ELSE ptototal END) AS ptototal,boomtotal,totalevents,accelerationtotal,brakingtotal
,corneringtotal,faulttotal,speedingtotal,maxspeed,lowbatterytotal,enginestarttotal,enginestoptotal,devicecode,firstcomm,accountid, EXTRACT(DAYOFWEEK FROM datename) AS dayname
FROM
  daily_summary s
  LEFT JOIN delta_summary d ON s.dailysummaryid=d.dailysummaryid
  LEFT JOIN event_summary e ON e.dailysummaryid=s.dailysummaryid
  LEFT JOIN (SELECT d.deviceidint, d.displayname, d.devicecode, d.accountid
    FROM device d) c ON c.deviceidint=s.deviceidint
  INNER JOIN (SELECT es.deviceidint, MIN(da.daystart) AS firstcomm FROM daily_summary da, event_summary es
    WHERE es.dailysummaryid=da.dailysummaryid and es.totalevents>0 GROUP BY es.deviceidint) ev ON ev.deviceidint=s.deviceidint
  INNER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid=l.accountid) l ON l.deviceidint=s.deviceidint
  FULL OUTER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid='dual_placeholder1') dual ON dual.deviceidint=s.deviceidint
where dual.deviceidint IS NULL

view: daily_vehicle_summary {
  derived_table: {
    sql: SELECT s.dailysummaryid, s.deviceidint, accountidint, displayname, daystart,dayend,timezone,datename,idletotal
,fueltotal,enginetotal,odometertotal,(CASE WHEN ptototal>idletotal THEN boomtotal ELSE ptototal END) AS ptototal,boomtotal,totalevents,accelerationtotal,brakingtotal
,corneringtotal,faulttotal,speedingtotal,maxspeed,lowbatterytotal,enginestarttotal,enginestoptotal,devicecode,firstcomm,accountid, EXTRACT(DAYOFWEEK FROM datename) AS dayname
FROM
  daily_summary s
  LEFT JOIN delta_summary d ON s.dailysummaryid=d.dailysummaryid
  LEFT JOIN event_summary e ON e.dailysummaryid=s.dailysummaryid
  LEFT JOIN (SELECT d.deviceidint, d.displayname, d.devicecode, d.accountid
    FROM device d) c ON c.deviceidint=s.deviceidint
  INNER JOIN (SELECT es.deviceidint, MIN(da.daystart) AS firstcomm FROM daily_summary da, event_summary es
    WHERE es.dailysummaryid=da.dailysummaryid and es.totalevents>0 GROUP BY es.deviceidint) ev ON ev.deviceidint=s.deviceidint
  INNER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid=l.accountid) l ON l.deviceidint=s.deviceidint
  FULL OUTER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid='dual_placeholder1') dual ON dual.deviceidint=s.deviceidint
where dual.deviceidint IS NULL ;;
  }

  dimension: dailysummaryid {
    type: number
    hidden: yes
    sql: ${TABLE}.dailysummaryid ;;
  }

  dimension: deviceidint {
    type: number
    hidden: yes
    sql: ${TABLE}.deviceidint ;;
  }

  dimension: accountidint {
    type: number
    hidden: yes
    sql: ${TABLE}.accountidint ;;
  }

  dimension: daystart {
    type: number
    hidden: yes
    sql: ${TABLE}.daystart ;;
  }

  dimension: dayend {
    type: number
    hidden: yes
    sql: ${TABLE}.dayend ;;
  }

  dimension: timezone {
    hidden: yes
    sql: ${TABLE}.timezone ;;
  }

  dimension: idletotal {
    type: number
    hidden: yes
    sql: ${TABLE}.idletotal ;;
  }

  dimension: idlehoursadjust {
    type: number
    hidden: yes
    sql: CASE
WHEN ${devicecode}='atrack' THEN ${idletotal}/60.0
ELSE ${idletotal} END ;;
  }

  dimension: idlehourscalc {
    label: "Unit Idle Time"
    description: "Unit Total Idle Hours By Day"
    type: number
    sql: CASE
WHEN ${idlehoursadjust}>${enginetotal} THEN ${enginetotal}
ELSE ${idlehoursadjust} END ;;
  }

  dimension: idlehoursnoboom {
    label: "Unit Idle Without Boom"
    description: "Unit Specific Idle Hours Without Boom By Day"
    type: number
    sql: ${idlehourscalc}-${boomtotal} ;;
  }

  dimension: idlehoursnopto {
    label: "Unit Idle Without PTO"
    description: "Unit Specific Idle Hours Without PTO By Day"
    type: number
    sql: ${idlehourscalc}-${ptototal} ;;
  }

  dimension: fueltotal {
    label: "Unit Fuel Gallons"
    description: "Unit Specific Fuel Consumption By Day"
    type: number
    sql: ${TABLE}.fueltotal*0.264172 ;;
  }

  dimension: enginetotal {
    label: "Unit Engine Run Time"
    description: "Unit Specific Engine Hours By Day"
    type: number
    sql: ${TABLE}.enginetotal ;;
  }

  dimension: enginehoursnoidle {
    label: "Unit Driving Time"
    description: "Unit Specific Driving Hours By Day"
    type: number
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${enginesum}-${idlesum} ;;
  }

  dimension: driving {
    label: "Unit Driving Time"
    description: "Unit Driving Hours By Day"
    type: number
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${enginetotal}-${idlehourscalc} ;;
  }

  dimension: odometertotal {
    label: "Unit Mileage"
    description: "Unit Specific Mileage By Day"
    type: number
    sql: ${TABLE}.odometertotal*0.621371 ;;
  }

  dimension: daily_fleet_used {
    label: "Day Used"
    description: "Unit Specific Usage By Day - Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven"
    type: int
    sql: CASE
WHEN ${odometertotal} > usage_threshold_miles THEN 1
ELSE 0 END ;;
  }

  dimension: ptototal {
    label: "Unit PTO Time"
    description: "Unit Specific PTO Hours By Day"
    type: number
    sql: ${TABLE}.ptototal ;;
  }

  dimension: ptonoboomtotal {
    label: "Unit PTO Time Without Boom"
    description: "Unit Specific PTO Hours Without Boom Hours By Day"
    type: number
    sql: ${ptototal}-${boomtotal} ;;
  }

  dimension: boomtotal {
    label: "Unit Boom Time"
    description: "Unit Specific Boom Hours By Day"
    type: number
    sql: ${TABLE}.boomtotal ;;
  }

  dimension: totalevents {
    type: number
    hidden: yes
    sql: ${TABLE}.totalevents ;;
  }

  dimension: accelerationtotal {
    label: "Unit Acceleration Events"
    description: "Unit Specific Acceleration Events By Day - Company default settings is an event when driver accelerates greater than acceleration_event_threshold"
    type: number
    sql: ${TABLE}.accelerationtotal ;;
  }

  dimension: brakingtotal {
    label: "Unit Braking Events"
    description: "Unit Specific Braking Events By Day - Company default settings is an event when driver accelerates greater than braking_event_threshold"
    type: number
    sql: ${TABLE}.brakingtotal ;;
  }

  dimension: corneringtotal {
    label: "Unit Cornering Events"
    description: "Unit Specific Cornering Events By Day - Company default settings is an event when the vehicle's centrifugal g-force is over the specific G-force threshold of 7usage_threshold_miles0mg for .1 seconds"
    type: number
    sql: ${TABLE}.corneringtotal ;;
  }

  dimension: faulttotal {
    label: "Unit Fault Code Events"
    description: "Unit Specific Fault Code Events By Day"
    type: number
    sql: ${TABLE}.faulttotal ;;
  }

  dimension: speedingtotal {
    label: "Unit Speeding Events"
    description: "Unit Specific Speeding Events By Day - Company default setting is an event exceeding 77mph for usage_threshold_miles seconds.  This event turns off when the vehicle goes below 7usage_threshold_miles mph for usage_threshold_miles seconds"
    type: number
    sql: ${TABLE}.speedingtotal ;;
  }

  dimension: maxspeed {
    label: "Maximum Unit Speed"
    description: "Maximum Speed By Vehicle By Day"
    type: number
    sql: ${TABLE}.maxspeed ;;
  }

  dimension: lowbatterytotal {
    label: "Unit Low Battery Events"
    description: "Unit Specific Low Battery Events By Day"
    type: number
    sql: ${TABLE}.lowbatterytotal ;;
  }

  dimension: enginestarttotal {
    type: number
    hidden: yes
    sql: ${TABLE}.enginestarttotal ;;
  }

  dimension: enginestoptotal {
    label: "Unit Engine Stops"
    description: "Unit Specific Engine Stops By Day"
    type: number
    sql: ${TABLE}.enginestoptotal ;;
  }

  dimension: usersvehicleid {
    label: "Unit Number"
    description: "Unit Number From Company Platform"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${TABLE}.displayname ;;
  }

  dimension: devicecode {
    hidden: yes
    sql: ${TABLE}.devicecode ;;
  }

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

  dimension: firstcomm {
    type: number
    hidden: yes
    sql: ${TABLE}.firstcomm ;;
  }

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

  dimension: gooddate {
    type: int
    hidden: yes
    sql: CASE
WHEN ${dayend} > ${firstcomm} THEN 1
ELSE 0 END ;;
  }

  dimension: weekend {
  }

  dimension_group: datename {
    label: "Day/Week/Month of the Year"
    description: "Select this field to aggregate data over this timeframe"
    type: time
    timeframes: [time, date, week, month]
    convert_tz: no
    sql: ${TABLE}.datename ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: idlesum {
    label: "Total Idle Without Boom"
    description: "Idle Hours Without Boom"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${idlehoursnoboom} ;;
  }

  measure: allidlesum {
    label: "Total Idle"
    description: "Idle Hours All Included"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${idlehourscalc} ;;
  }

  measure: idlesumnopto {
    label: "Idle Time"
    description: "Idle Hours Without PTO"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${idlehoursnopto} ;;
  }

  measure: fuelsum {
    label: "Total Fuel Gallons"
    description: "Total Fuel Gallons Over Specified Timeframe"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${fueltotal} ;;
  }

  measure: enginesum {
    label: "Total Engine Hours"
    description: "Total Engine Hours Over Specified Timeframe"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${enginetotal} ;;
  }

  measure: idlepercentage {
    label: "Idling Percentage Without Boom"
    description: "Total Idle Hours Without Boom Usage/ Total Engine Run Time  Over Specified Timeframe"
    type: number
    value_format: "##.0%"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${idlesum}/NULLIF(${enginesum},0) ;;
  }

  measure: allidlepercentage {
    label: "Idling Percentage With Boom"
    description: "Total Idle Hours / Total Engine Run Time  Over Specified Timeframe"
    type: number
    value_format: "##.0%"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${allidlesum}/NULLIF(${enginesum},0) ;;
  }

  measure: idlepercentagewithoutpto {
    label: "Idling Percentage"
    description: "Total Idle Hours Without PTO / Total Engine Run Time  Over Specified Timeframe"
    type: number
    value_format: "##.0%"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${idlesumnopto}/NULLIF(${enginesum},0) ;;
  }

  measure: idleboompercentage {
    label: "Idle Percentage Only Boom"
    description: "Total Idle Hours From Boom Usage / Total Engine Run Time  Over Specified Timeframe"
    type: number
    value_format: "##.0%"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${boomsum}/NULLIF(${enginesum},0) ;;
  }

  measure: idleptonoboompercentage {
    label: "Idle Percentage From PTO Without Boom"
    description: "Total Idle Hours From PTO Without Boom Usage / Total Engine Run Time  Over Specified Timeframe"
    type: number
    value_format: "##.0%"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${ptosum}/NULLIF(${enginesum},0) ;;
  }

  measure: drivingsum {
    label: "Driving Time"
    description: "Driving Hours"
    type: number
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${enginesum}-${allidlesum} ;;
  }

  measure: drivingaverage {
    label: "Average Driving Time"
    description: "Average Driving Hours"
    type: average
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: (${enginetotal}-${idlehourscalc}) ;;
  }

  measure: odometersum {
    label: "Total Mileage"
    description: "Total Mileage Over Specified Timeframe"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${odometertotal} ;;
  }

  measure: ptosum {
    label: "PTO Time Without Boom"
    description: "PTO Hours Without BoomOver Specified Timeframe"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${ptonoboomtotal} ;;
  }

  measure: boomsum {
    label: "Boom Time"
    description: "Boom Hours Over Specified Timeframe"
    type: sum
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${boomtotal} ;;
  }

  measure: accelsum {
    label: "Total Acceleration Events"
    description: "Total Acceleration Events Over Specified Timeframe - Company default settings is an event when driver accelerates greater than acceleration_event_threshold"
    type: sum
    value_format: "0.00"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${accelerationtotal} ;;
  }

  measure: accelerationscore {
    label: "Total Acceleration Score"
    description: "Total Acceleration Score = Number of Acceleration Events Per 100 Driving Hours"
    type: number
    value_format: "0.00"
    drill_fields: [usersvehicleid, totalscore, brakingscore, corneringscore, speedingscore, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum]
    sql: ((${accelsum}*1.0)/NULLIF(${drivingsum},0))*100.0 ;;
  }

  measure: brakesum {
    label: "Total Braking Events"
    description: "Total Braking Events Over Specified Timeframe - Company default settings is an event when driver accelerates greater than braking_event_threshold"
    type: sum
    value_format: "0.00"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${brakingtotal} ;;
  }

  measure: brakingscore {
    label: "Total Braking Score"
    description: "Total Braking Score = Number of Braking Events Per 100 Driving Hours"
    type: number
    value_format: "0.00"
    drill_fields: [usersvehicleid, totalscore, brakingscore, corneringscore, speedingscore, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum]
    sql: ((${brakesum}*1.0)/NULLIF(${drivingsum},0))*100.0 ;;
  }

  measure: cornersum {
    label: "Total Cornering Events"
    description: "Total Cornering Events Over Specified Timeframe - Company default settings is an event when the vehicle's centrifugal g-force is over the specific G-force threshold of 7usage_threshold_miles0mg for .1 seconds"
    type: sum
    value_format: "0.00"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${corneringtotal} ;;
  }

  measure: corneringscore {
    label: "Total Cornering Score"
    description: "Total Cornering Score = Number of Cornering Events Per 100 Driving Hours"
    type: number
    value_format: "0.00"
    drill_fields: [usersvehicleid, totalscore, brakingscore, corneringscore, speedingscore, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum]
    sql: ((${cornersum}*1.0)/NULLIF(${drivingsum},0))*100.0 ;;
  }

  measure: speedsum {
    label: "Total Speeding Events"
    description: "Total Speeding Events Over Specified Timeframe - Company default setting is an event exceeding 77mph for usage_threshold_miles seconds.  This event turns off when the vehicle goes below 7usage_threshold_miles mph for usage_threshold_miles seconds"
    type: sum
    value_format: "0.00"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${speedingtotal} ;;
  }

  measure: speedingscore {
    label: "Total Speeding Score"
    description: "Total Speeding Score = Number of Speeding Events Per 100 Driving Hours"
    type: number
    value_format: "0.00"
    drill_fields: [usersvehicleid, totalscore, brakingscore, corneringscore, speedingscore, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum]
    sql: ((${speedsum}*1.0)/NULLIF(${drivingsum},0))*100.0 ;;
  }

  measure: totalscore {
    label: "Total Safety Score"
    description: "Total Satefy Score = Acceleration Score + Braking Score + Cornering Score + Speeding Score"
    type: number
    value_format: "0.00"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${speedingscore}+${corneringscore} ;;
  }

  measure: usedsum {
    label: "Total Days Used"
    description: "Total Days Used Over Timeframe- Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven"
    type: sum
    value_format: "#,##0"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${daily_fleet_used} ;;
  }

  measure: total_days_selected {
    type: sum
    hidden: yes
    value_format: "#,##0"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, accelsum, brakesum, cornersum, speedsum]
    sql: ${gooddate} ;;
  }

  measure: usage_rate {
    label: "Usage Rate"
    description: "Percentage of Used Vehicles Over Timeframe - Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven"
    type: number
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, usage_rate]
    sql: ROUND(100.0*${usedsum}/NULLIF(${total_days_selected},0),1) ;;
  }

  measure: unused_rate {
    label: "Unused Rate"
    description: "Percentage of Unused Vehicles Over Timeframe - Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven"
    type: number
    value_format: "0.00\%"
    drill_fields: [usersvehicleid, usedsum, odometersum, enginesum, fuelsum, ptosum, boomsum, idlesum, usage_rate]
    sql: 100-ROUND(100.0*${usedsum}/NULLIF(${total_days_selected},0),1) ;;
  }
}

- view: daily_vehicle_summary
  derived_table:
    sql: SELECT s.dailysummaryid, s.deviceidint, accountidint, displayname, daystart,dayend,timezone,datename,idletotal
,fueltotal,enginetotal,odometertotal,(CASE WHEN ptototal>idletotal THEN boomtotal ELSE ptototal END) AS ptototal,boomtotal,totalevents,accelerationtotal,brakingtotal
,corneringtotal,faulttotal,speedingtotal,maxspeed,lowbatterytotal,enginestarttotal,enginestoptotal,devicecode,firstcomm,accountid, EXTRACT(DAYOFWEEK FROM datename) AS dayname
FROM
  daily_summary s
  LEFT JOIN delta_summary d ON s.dailysummaryid=d.dailysummaryid
  LEFT JOIN event_summary e ON e.dailysummaryid=s.dailysummaryid
  LEFT JOIN (SELECT d.deviceidint, d.displayname, d.devicecode, d.accountid
    FROM device d) c ON c.deviceidint=s.deviceidint
  INNER JOIN (SELECT es.deviceidint, MIN(da.daystart) AS firstcomm FROM daily_summary da, event_summary es
    WHERE es.dailysummaryid=da.dailysummaryid and es.totalevents>0 GROUP BY es.deviceidint) ev ON ev.deviceidint=s.deviceidint
  INNER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid=l.accountid) l ON l.deviceidint=s.deviceidint
  FULL OUTER JOIN (SELECT l.deviceidint, l.groupid FROM devicelist l WHERE l.groupid='dual_placeholder1') dual ON dual.deviceidint=s.deviceidint
where dual.deviceidint IS NULL
  fields:
  - dimension: dailysummaryid
    type: number
    hidden: true
    sql: ${TABLE}.dailysummaryid
  - dimension: deviceidint
    type: number
    hidden: true
    sql: ${TABLE}.deviceidint
  - dimension: accountidint
    type: number
    hidden: true
    sql: ${TABLE}.accountidint
  - dimension: daystart
    type: number
    hidden: true
    sql: ${TABLE}.daystart
  - dimension: dayend
    type: number
    hidden: true
    sql: ${TABLE}.dayend
  - dimension: timezone
    hidden: true
    sql: ${TABLE}.timezone
  - dimension: idletotal
    type: number
    hidden: true
    sql: ${TABLE}.idletotal
  - dimension: idlehoursadjust
    type: number
    hidden: true
    sql: CASE
WHEN ${devicecode}='atrack' THEN ${idletotal}/60.0
ELSE ${idletotal} END
  - dimension: idlehourscalc
    type: number
    label: Unit Idle Time
    description: Unit Total Idle Hours By Day
    sql: CASE
WHEN ${idlehoursadjust}>${enginetotal} THEN ${enginetotal}
ELSE ${idlehoursadjust} END
  - dimension: idlehoursnoboom
    type: number
    label: Unit Idle Without Boom
    description: Unit Specific Idle Hours Without Boom By Day
    sql: ${idlehourscalc}-${boomtotal}
  - dimension: idlehoursnopto
    type: number
    label: Unit Idle Without PTO
    description: Unit Specific Idle Hours Without PTO By Day
    sql: ${idlehourscalc}-${ptototal}
  - dimension: fueltotal
    type: number
    label: Unit Fuel Gallons
    description: Unit Specific Fuel Consumption By Day
    sql: ${TABLE}.fueltotal*0.264172
  - dimension: enginetotal
    type: number
    label: Unit Engine Run Time
    description: Unit Specific Engine Hours By Day
    sql: ${TABLE}.enginetotal
  - dimension: enginehoursnoidle
    type: number
    label: Unit Driving Time
    description: Unit Specific Driving Hours By Day
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${enginesum}-${idlesum}
  - dimension: driving
    type: number
    label: Unit Driving Time
    description: Unit Driving Hours By Day
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${enginetotal}-${idlehourscalc}
  - dimension: odometertotal
    type: number
    label: Unit Mileage
    description: Unit Specific Mileage By Day
    sql: ${TABLE}.odometertotal*0.621371
  - dimension: daily_fleet_used
    type: int
    label: Day Used
    description: Unit Specific Usage By Day - Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven
    sql: CASE
WHEN ${odometertotal} > usage_threshold_miles THEN 1
ELSE 0 END
  - dimension: ptototal
    type: number
    label: Unit PTO Time
    description: Unit Specific PTO Hours By Day
    sql: ${TABLE}.ptototal
  - dimension: ptonoboomtotal
    type: number
    label: Unit PTO Time Without Boom
    description: Unit Specific PTO Hours Without Boom Hours By Day
    sql: ${ptototal}-${boomtotal}
  - dimension: boomtotal
    type: number
    label: Unit Boom Time
    description: Unit Specific Boom Hours By Day
    sql: ${TABLE}.boomtotal
  - dimension: totalevents
    type: number
    hidden: true
    sql: ${TABLE}.totalevents
  - dimension: accelerationtotal
    type: number
    label: Unit Acceleration Events
    description: Unit Specific Acceleration Events By Day - Company default settings is an event when driver accelerates greater than acceleration_event_threshold
    sql: ${TABLE}.accelerationtotal
  - dimension: brakingtotal
    type: number
    label: Unit Braking Events
    description: Unit Specific Braking Events By Day - Company default settings is an event when driver accelerates greater than braking_event_threshold
    sql: ${TABLE}.brakingtotal
  - dimension: corneringtotal
    type: number
    label: Unit Cornering Events
    description: Unit Specific Cornering Events By Day - Company default settings is an event when the vehicle's centrifugal g-force is over the specific G-force threshold of 7usage_threshold_miles0mg for .1 seconds
    sql: ${TABLE}.corneringtotal
  - dimension: faulttotal
    type: number
    label: Unit Fault Code Events
    description: Unit Specific Fault Code Events By Day
    sql: ${TABLE}.faulttotal
  - dimension: speedingtotal
    type: number
    label: Unit Speeding Events
    description: Unit Specific Speeding Events By Day - Company default setting is an event exceeding 77mph for usage_threshold_miles seconds.  This event turns off when the vehicle goes below 7usage_threshold_miles mph for usage_threshold_miles seconds
    sql: ${TABLE}.speedingtotal
  - dimension: maxspeed
    type: number
    label: Maximum Unit Speed
    description: Maximum Speed By Vehicle By Day
    sql: ${TABLE}.maxspeed
  - dimension: lowbatterytotal
    type: number
    label: Unit Low Battery Events
    description: Unit Specific Low Battery Events By Day
    sql: ${TABLE}.lowbatterytotal
  - dimension: enginestarttotal
    type: number
    hidden: true
    sql: ${TABLE}.enginestarttotal
  - dimension: enginestoptotal
    type: number
    label: Unit Engine Stops
    description: Unit Specific Engine Stops By Day
    sql: ${TABLE}.enginestoptotal
  - dimension: usersvehicleid
    label: Unit Number
    description: Unit Number From Company Platform
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${TABLE}.displayname
  - dimension: devicecode
    hidden: true
    sql: ${TABLE}.devicecode
  - dimension: accountid
    sql: ${TABLE}.accountid
  - dimension: firstcomm
    type: number
    hidden: true
    sql: ${TABLE}.firstcomm
  - dimension: dayname
    type: number
    hidden: true
    sql: ${TABLE}.dayname
  - dimension: gooddate
    type: int
    hidden: true
    sql: CASE
WHEN ${dayend} > ${firstcomm} THEN 1
ELSE 0 END
  - dimension: weekend
  - dimension_group: datename
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.datename
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: idlesum
    type: sum
    label: Total Idle Without Boom
    description: Idle Hours Without Boom
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${idlehoursnoboom}
  - measure: allidlesum
    type: sum
    label: Total Idle
    description: Idle Hours All Included
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${idlehourscalc}
  - measure: idlesumnopto
    type: sum
    label: Idle Time
    description: Idle Hours Without PTO
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${idlehoursnopto}
  - measure: fuelsum
    type: sum
    label: Total Fuel Gallons
    description: Total Fuel Gallons Over Specified Timeframe
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${fueltotal}
  - measure: enginesum
    type: sum
    label: Total Engine Hours
    description: Total Engine Hours Over Specified Timeframe
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${enginetotal}
  - measure: idlepercentage
    type: number
    label: Idling Percentage Without Boom
    description: Total Idle Hours Without Boom Usage/ Total Engine Run Time  Over Specified Timeframe
    value_format: '##.0%'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${idlesum}/NULLIF(${enginesum},0)
  - measure: allidlepercentage
    type: number
    label: Idling Percentage With Boom
    description: Total Idle Hours / Total Engine Run Time  Over Specified Timeframe
    value_format: '##.0%'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${allidlesum}/NULLIF(${enginesum},0)
  - measure: idlepercentagewithoutpto
    type: number
    label: Idling Percentage
    description: Total Idle Hours Without PTO / Total Engine Run Time  Over Specified Timeframe
    value_format: '##.0%'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${idlesumnopto}/NULLIF(${enginesum},0)
  - measure: idleboompercentage
    type: number
    label: Idle Percentage Only Boom
    description: Total Idle Hours From Boom Usage / Total Engine Run Time  Over Specified Timeframe
    value_format: '##.0%'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${boomsum}/NULLIF(${enginesum},0)
  - measure: idleptonoboompercentage
    type: number
    label: Idle Percentage From PTO Without Boom
    description: Total Idle Hours From PTO Without Boom Usage / Total Engine Run Time  Over Specified Timeframe
    value_format: '##.0%'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${ptosum}/NULLIF(${enginesum},0)
  - measure: drivingsum
    type: number
    label: Driving Time
    description: Driving Hours
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${enginesum}-${allidlesum}
  - measure: drivingaverage
    type: average
    label: Average Driving Time
    description: Average Driving Hours
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: (${enginetotal}-${idlehourscalc})
  - measure: odometersum
    type: sum
    label: Total Mileage
    description: Total Mileage Over Specified Timeframe
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${odometertotal}
  - measure: ptosum
    type: sum
    label: PTO Time Without Boom
    description: PTO Hours Without BoomOver Specified Timeframe
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${ptonoboomtotal}
  - measure: boomsum
    type: sum
    label: Boom Time
    description: Boom Hours Over Specified Timeframe
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${boomtotal}
  - measure: accelsum
    type: sum
    label: Total Acceleration Events
    description: Total Acceleration Events Over Specified Timeframe - Company default settings is an event when driver accelerates greater than acceleration_event_threshold
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${accelerationtotal}
  - measure: accelerationscore
    type: number
    label: Total Acceleration Score
    description: Total Acceleration Score = Number of Acceleration Events Per 100 Driving Hours
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - totalscore
    - brakingscore
    - corneringscore
    - speedingscore
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    sql: ((${accelsum}*1.0)/NULLIF(${drivingsum},0))*100.0
  - measure: brakesum
    type: sum
    label: Total Braking Events
    description: Total Braking Events Over Specified Timeframe - Company default settings is an event when driver accelerates greater than braking_event_threshold
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${brakingtotal}
  - measure: brakingscore
    type: number
    label: Total Braking Score
    description: Total Braking Score = Number of Braking Events Per 100 Driving Hours
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - totalscore
    - brakingscore
    - corneringscore
    - speedingscore
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    sql: ((${brakesum}*1.0)/NULLIF(${drivingsum},0))*100.0
  - measure: cornersum
    type: sum
    label: Total Cornering Events
    description: Total Cornering Events Over Specified Timeframe - Company default settings is an event when the vehicle's centrifugal g-force is over the specific G-force threshold of 7usage_threshold_miles0mg for .1 seconds
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${corneringtotal}
  - measure: corneringscore
    type: number
    label: Total Cornering Score
    description: Total Cornering Score = Number of Cornering Events Per 100 Driving Hours
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - totalscore
    - brakingscore
    - corneringscore
    - speedingscore
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    sql: ((${cornersum}*1.0)/NULLIF(${drivingsum},0))*100.0
  - measure: speedsum
    type: sum
    label: Total Speeding Events
    description: Total Speeding Events Over Specified Timeframe - Company default setting is an event exceeding 77mph for usage_threshold_miles seconds.  This event turns off when the vehicle goes below 7usage_threshold_miles mph for usage_threshold_miles seconds
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${speedingtotal}
  - measure: speedingscore
    type: number
    label: Total Speeding Score
    description: Total Speeding Score = Number of Speeding Events Per 100 Driving Hours
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - totalscore
    - brakingscore
    - corneringscore
    - speedingscore
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    sql: ((${speedsum}*1.0)/NULLIF(${drivingsum},0))*100.0
  - measure: totalscore
    type: number
    label: Total Safety Score
    description: Total Satefy Score = Acceleration Score + Braking Score + Cornering Score + Speeding Score
    value_format: 0.00
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${speedingscore}+${corneringscore}
  - measure: usedsum
    type: sum
    label: Total Days Used
    description: Total Days Used Over Timeframe- Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven
    value_format: '#,##0'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${daily_fleet_used}
  - measure: total_days_selected
    type: sum
    value_format: '#,##0'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - accelsum
    - brakesum
    - cornersum
    - speedsum
    sql: ${gooddate}
  - measure: usage_rate
    type: number
    label: Usage Rate
    description: Percentage of Used Vehicles Over Timeframe - Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - usage_rate
    sql: ROUND(100.0*${usedsum}/NULLIF(${total_days_selected},0),1)
  - measure: unused_rate
    type: number
    label: Unused Rate
    description: Percentage of Unused Vehicles Over Timeframe - Company Defines Usage As a Day Exceeding usage_threshold_miles Miles Driven
    value_format: '0.00\%'
    drill_fields:
    - usersvehicleid
    - usedsum
    - odometersum
    - enginesum
    - fuelsum
    - ptosum
    - boomsum
    - idlesum
    - usage_rate
    sql: 100-ROUND(100.0*${usedsum}/NULLIF(${total_days_selected},0),1)