SQL Delivery service

F Routespmt

F Routespmt model for delivery service.

source-preserved

Blocks

dbt SQL LKML LookML

f_routespmt.sql · 115 lines

inferred

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

Select rpmt.*
      ,aj.*
      ,pes_2.shipment_companyeries_count
      ,pes_2.orders_count
      ,pes_2.pickup_event_count
  From
    (Select
       r.length as scheduled_time
      ,r.metro_id as metro_id
      ,r.id as route_id
      ,r.start_at as route_start_at
      ,r.driver_id as user_id
      ,DATE_ADD(start_at,INTERVAL length minute) AS oncall_end_at
      ,tt.stops
      ,tt.pickups
      ,tt.dropoffs
      ,tt.returns
      ,tt.in_route_moves
      ,tt.first_move_distance
      ,tt.in_routes_distance
      ,tt.pickups_time
      ,tt.dropoffs_time
      ,tt.first_move_time
      ,tt.in_routes_move_time
      ,tt.shipment_company_time
      ,tt.avg_distance
      ,tt.max_distance
      ,tt.min_distance
      ,tt.end_pt
      ,tt.start_pt
      ,r.worked_minutes
    FROM routes r
    LEFT JOIN {{ ref('f_tasktemplate') }} as tt
      on r.id=tt.route_id
    Where DATE(DATE_ADD(r.start_at,INTERVAL -8 hour)) > '2014-01-01'
    And r.type='Route'
    ) rpmt
 LEFT JOIN {{ ref('f_adjustments') }} as aj
   on rpmt.route_id=aj.base_route_id
 LEFT JOIN
      (Select
               pes_1.route_id
              ,count(distinct shipment_companyery_id) as shipment_companyeries_count
              ,count(distinct order_id) as orders_count
              ,count(distinct pickup_proxy) as pickup_event_count
          From
            (SELECT
                 d.route_id
                ,d.id as shipment_companyery_id
                ,d.order_id
                ,p1.pickup_group
                ,case when p1.pickup_group IS NOT NULL THEN p1.pickup_group
                     else d.id
                         end pickup_proxy
              FROM shipment_companyeries d
              LEFT JOIN {{ ref('grouped_shipment_companyery_summary') }} p1
                ON p1.shipment_companyery_id=d.id
            Group By 1,2,3,4,5
            ) pes_1
       Group By 1
      ) pes_2
   on rpmt.route_id = pes_2.route_id

# models/schema.yml
version: 2

models:
  - name: f_routespmt
    columns:
      - name: pickup_event_count
      - name: orders_count
      - name: base_route_id
        tests:
          - unique
          - not_null
      - name: bonus
      - name: hour_additions
      - name: mile_additions
      - name: mile_deductions
      - name: premium_hour_additions
      - name: noshowrate
      - name: scheduled_time
      - name: total_scheduled_time_h
      - name: min_pmt_qt
      - name: min_pmt_min
      - name: metro_id
      - name: metros_name_community
      - name: route_id
      - name: user_id
      - name: shipment_companyeries
      - name: total_shipment_companyeries
      - name: stops
      - name: total_stops
      - name: pickups
      - name: returns
      - name: avg_stops_per_pickup
      - name: avg_orders_per_pickup
      - name: dropoffs
      - name: in_route_moves
      - name: first_move_distance
      - name: in_routes_distance
      - name: miles
      - name: pickups_time
      - name: dropoffs_time
      - name: first_move_time
      - name: in_routes_move_time
      - name: avg_distance
      - name: min_distance
      - name: max_distance
      - name: laststop_distance
      - name: worked_time_hrs
      - name: actual_shipment_company_time

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.

f_routespmt.sql · 62 lines

source-preserved validation: not checked

Select rpmt.*
      ,aj.*
      ,pes_2.shipment_companyeries_count
      ,pes_2.orders_count
      ,pes_2.pickup_event_count
  From
    (Select
       r.length as scheduled_time
      ,r.metro_id as metro_id
      ,r.id as route_id
      ,r.start_at as route_start_at
      ,r.driver_id as user_id
      ,DATE_ADD(start_at,INTERVAL length minute) AS oncall_end_at
      ,tt.stops
      ,tt.pickups
      ,tt.dropoffs
      ,tt.returns
      ,tt.in_route_moves
      ,tt.first_move_distance
      ,tt.in_routes_distance
      ,tt.pickups_time
      ,tt.dropoffs_time
      ,tt.first_move_time
      ,tt.in_routes_move_time
      ,tt.shipment_company_time
      ,tt.avg_distance
      ,tt.max_distance
      ,tt.min_distance
      ,tt.end_pt
      ,tt.start_pt
      ,r.worked_minutes
    FROM routes r
    LEFT JOIN ${f_tasktemplate.SQL_TABLE_NAME} as tt
      on r.id=tt.route_id
    Where DATE(DATE_ADD(r.start_at,INTERVAL -8 hour)) > '2014-01-01'
    And r.type='Route'
    ) rpmt
 LEFT JOIN ${f_adjustments.SQL_TABLE_NAME} as aj
   on rpmt.route_id=aj.base_route_id
 LEFT JOIN
      (Select
               pes_1.route_id
              ,count(distinct shipment_companyery_id) as shipment_companyeries_count
              ,count(distinct order_id) as orders_count
              ,count(distinct pickup_proxy) as pickup_event_count
          From
            (SELECT
                 d.route_id
                ,d.id as shipment_companyery_id
                ,d.order_id
                ,p1.pickup_group
                ,case when p1.pickup_group IS NOT NULL THEN p1.pickup_group
                     else d.id
                         end pickup_proxy
              FROM shipment_companyeries d
              LEFT JOIN ${grouped_shipment_companyery_summary.SQL_TABLE_NAME} p1
                ON p1.shipment_companyery_id=d.id
            Group By 1,2,3,4,5
            ) pes_1
       Group By 1
      ) pes_2
   on rpmt.route_id = pes_2.route_id

f_routespmt.view.lkml · 526 lines

inferred

view: f_routespmt {
  derived_table: {
    sql: Select rpmt.*
      ,aj.*
      ,pes_2.shipment_companyeries_count
      ,pes_2.orders_count
      ,pes_2.pickup_event_count
  From
    (Select
       r.length as scheduled_time
      ,r.metro_id as metro_id
      ,r.id as route_id
      ,r.start_at as route_start_at
      ,r.driver_id as user_id
      ,DATE_ADD(start_at,INTERVAL length minute) AS oncall_end_at
      ,tt.stops
      ,tt.pickups
      ,tt.dropoffs
      ,tt.returns
      ,tt.in_route_moves
      ,tt.first_move_distance
      ,tt.in_routes_distance
      ,tt.pickups_time
      ,tt.dropoffs_time
      ,tt.first_move_time
      ,tt.in_routes_move_time
      ,tt.shipment_company_time
      ,tt.avg_distance
      ,tt.max_distance
      ,tt.min_distance
      ,tt.end_pt
      ,tt.start_pt
      ,r.worked_minutes
    FROM routes r
    LEFT JOIN ${f_tasktemplate.SQL_TABLE_NAME} as tt
      on r.id=tt.route_id
    Where DATE(DATE_ADD(r.start_at,INTERVAL -8 hour)) > '2014-01-01'
    And r.type='Route'
    ) rpmt
 LEFT JOIN ${f_adjustments.SQL_TABLE_NAME} as aj
   on rpmt.route_id=aj.base_route_id
 LEFT JOIN
      (Select
               pes_1.route_id
              ,count(distinct shipment_companyery_id) as shipment_companyeries_count
              ,count(distinct order_id) as orders_count
              ,count(distinct pickup_proxy) as pickup_event_count
          From
            (SELECT
                 d.route_id
                ,d.id as shipment_companyery_id
                ,d.order_id
                ,p1.pickup_group
                ,case when p1.pickup_group IS NOT NULL THEN p1.pickup_group
                     else d.id
                         end pickup_proxy
              FROM shipment_companyeries d
              LEFT JOIN ${grouped_shipment_companyery_summary.SQL_TABLE_NAME} p1
                ON p1.shipment_companyery_id=d.id
            Group By 1,2,3,4,5
            ) pes_1
       Group By 1
      ) pes_2
   on rpmt.route_id = pes_2.route_id ;;
  }

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

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

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

  dimension: bonus {
    type: number
    sql: IFNULL(${driver_payments.bonus_settlement},0) ;;
  }

  dimension: hour_additions {
    type: number
    sql: IFNULL(${TABLE}.hour_additions,0) ;;
  }

  dimension: mile_additions {
    type: number
    sql: IFNULL(${TABLE}.mile_additions,0) ;;
  }

  dimension: mile_deductions {
    type: number
    sql: IFNULL(${TABLE}.mile_deductions,0) ;;
  }

  dimension: premium_hour_additions {
    type: number
    sql: IFNULL(${TABLE}.premium_hour_additions,0) ;;
  }

  dimension: noshowrate {
    type: percentage
    sql: ${driver_time_slots.noshowrate} ;;
  }

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

  dimension: total_scheduled_time_h {
    type: sum
    sql: ${TABLE}.scheduled_time/60 ;;
  }

  dimension: min_pmt_qt {
    type: number
    sql: ${scheduled_time}/30 ;;
  }

  dimension: min_pmt_min {
    type: number
    sql: ${scheduled_time}/2 ;;
  }

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

  dimension: metros_name_community {
    sql: ${metros.name_community} ;;
  }

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

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

  dimension: shipment_companyeries {
    type: number
    sql: ${billable_tracking_codes_per_route.billable_tracking_code_count} ;;
  }

  dimension: total_shipment_companyeries {
    type: sum
    sql: ${shipment_companyeries} ;;
  }

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

  dimension: total_stops {
    type: sum
    sql: ${TABLE}.stops ;;
  }

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

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

  dimension: avg_stops_per_pickup {
    type: number
    sql: ${total_stops}/${total_pickups}) ;;
  }

  dimension: avg_orders_per_pickup {
    type: number
    sql: ${total_shipment_companyeries}/${total_pickups} ;;
  }

  dimension: dropoffs {
    type: number
    sql: ${TABLE}.stops ;;
  }

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

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

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

  dimension: miles {
    type: number
    sql: ${driver_payments.paid_miles} ;;
  }

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

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

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

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

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

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

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

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

  dimension: worked_time_hrs {
    type: number
    sql: (${TABLE}.worked_minutes /60) ;;
  }

  dimension: actual_shipment_company_time {
    type: number
    sql: ${TABLE}.worked_minutes ;;
  }

  dimension: actual_total_shipment_company_time_h {
    type: sum
    sql: ${actual_shipment_company_time}/60 ;;
  }

  dimension: total_paid_time_h {
    type: sum
    sql: ${driver_payments.paid_hours} ;;
  }

  dimension: pmt_distance {
    type: number
    sql: ${driver_payments.mileage_settlement} ;;
  }

  dimension: premiums {
    type: number
    sql: IFNULL(${driver_payments.premiums_settlement},0) ;;
  }

  dimension: time_cost {
    type: sum
    sql: ${pmt_time} ;;
  }

  dimension: gas_cost {
    type: sum
    sql: ${pmt_distance} ;;
  }

  dimension: pdistance_cost {
    type: sum
    sql: IFNULL(${bonus_distance},0) ;;
  }

  dimension: ptime_cost {
    type: sum
    sql: IFNULL(${bonus_time},0) ;;
  }

  dimension: pbonus_cost {
    type: sum
    sql: IFNULL(${bonus},0) ;;
  }

  dimension: driver_utilization {
    type: percentage
    sql: ${total_paid_time_h}/${total_scheduled_time_h} ;;
  }

  dimension: route_utilization {
    type: percentage
    sql: ${actual_total_shipment_company_time_h}/${total_paid_time_h} ;;
  }

  dimension: capacity_utilization {
    type: percentage
    sql: ${actual_total_shipment_company_time_h}/${total_scheduled_time_h} ;;
  }

  dimension: total_pickups {
    type: sum
    sql: ${TABLE}.pickups ;;
  }

  dimension: total_pickups_time {
    type: sum
    sql: ${TABLE}.pickups_time ;;
  }

  dimension: avg_pickup_time_m {
    type: number
    sql: ${total_pickups_time}/${total_pickups} ;;
  }

  dimension: total_dropoffs {
    type: sum
    sql: ${TABLE}.dropoffs ;;
  }

  dimension: total_dropoffs_time {
    type: sum
    sql: ${TABLE}.dropoffs_time ;;
  }

  dimension: avg_dropoff_time_m {
    type: number
    sql: ${total_dropoffs_time}/${total_dropoffs} ;;
  }

  dimension: costpershipment_company {
    type: number
    sql: ${total_settlements}/${total_shipment_companyeries} ;;
  }

  dimension: shipment_companyperhour {
    type: number
    sql: ${total_shipment_companyeries}/${actual_total_shipment_company_time_h} ;;
  }

  dimension: shipment_companyperpaidhour {
    type: number
    sql: ${total_shipment_companyeries}/${total_paid_time_h} ;;
  }

  dimension_group: route_start_at {
    type: time
    timeframes: [time, date, week, month, dow]
    sql: ${TABLE}.route_start_at ;;
  }

  dimension_group: route_start_at_local {
    type: time
    timeframes: [time, date, hod, week, month, dow_num, year]
    sql: case
  when ${metros.id} in (24) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/Hawaii')
  when ${metros.id} in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/PACIFIC')
  when ${metros.id} in (2,7,15,16,17,23,35) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/CENTRAL')
  when ${metros.id} in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/Eastern')
end ;;
  }

  dimension_group: oncall_end_at {
    type: time
    timeframes: [time]
    sql: ${TABLE}.oncall_end_at ;;
  }

  dimension_group: end_pt {
    type: time
    timeframes: [time, date]
  }

  dimension_group: start_pt {
    type: time
    timeframes: [time, date]
  }

  measure: count {
    type: count
  }

  measure: total_pickup_event_count {
    label: "total_pickup_event_dontuse"
    type: sum
    sql: ${pickup_event_count} ;;
  }

  measure: total_orders_count {
    type: sum
    sql: ${orders_count} ;;
  }

  measure: total_stop_events {
    label: "total_stop_events_dontuse"
    type: number
    sql: ${total_dropoffs} + ${total_pickup_event_count} ;;
  }

  measure: orders_per_pickup {
    type: number
    sql: ${total_shipment_companyeries} / ${total_pickups} ;;
  }

  measure: route_count {
    type: count_distinct
    sql: ${route_id} ;;
  }

  measure: shipment_companyroute_count {
    type: count_distinct
    filters: [shipment_companyeries: "not 0"]
    sql: ${route_id} ;;
  }

  measure: empty_route_count {
    type: number
    sql: ${route_count} - ${shipment_companyroute_count} ;;
  }

  measure: sum_total_scheduled_time_h {
    type: sum
    sql: ${total_scheduled_time_h} ;;
  }

  measure: average_scheduled_time_per_user {
    type: number
    sql: ${total_scheduled_time_h}/${driver_count} ;;
  }

  measure: orders_per_route {
    type: number
    sql: ${total_shipment_companyeries}/${route_count} ;;
  }

  measure: total_miles {
    type: sum
    sql: ${driver_payments.paid_miles} ;;
  }

  measure: total_worked_time {
    type: sum
    sql: ${worked_time_hrs} ;;
  }

  measure: in_routes_distance_sum {
    type: sum
    sql: ${in_routes_distance} ;;
  }

  measure: total_settlements {
    type: sum
    sql: ${driver_payments.total_settlement} ;;
  }

  measure: total_premiums {
    type: sum
    sql: ${premiums} ;;
  }

  measure: stops_per_paid_hour {
    type: number
    sql: ${total_stops}/${total_paid_time_h} ;;
  }

  measure: driver_count {
    type: count_distinct
    sql: ${user_id} ;;
  }

  measure: averageshipment_companyperuser {
    type: number
    sql: ${total_shipment_companyeries}/${driver_count} ;;
  }

  measure: shipment_companyperroute {
    type: number
    sql: ${total_shipment_companyeries}/${shipment_companyroute_count} ;;
  }

  measure: milesperroute {
    type: number
    sql: ${total_miles}/${shipment_companyroute_count} ;;
  }

  measure: minsperroute {
    type: number
    sql: 60*${actual_total_shipment_company_time_h}/${shipment_companyroute_count} ;;
  }

  measure: milespershipment_company {
    type: number
    sql: ${total_miles}/${total_shipment_companyeries} ;;
  }

  measure: minspershipment_company {
    type: number
    sql: 60*${actual_total_shipment_company_time_h}/${total_shipment_companyeries} ;;
  }
}

f_routespmt.view.lookml · 363 lines

source-preserved

- view: f_routespmt
  derived_table:
    sql: Select rpmt.*
      ,aj.*
      ,pes_2.shipment_companyeries_count
      ,pes_2.orders_count
      ,pes_2.pickup_event_count
  From
    (Select
       r.length as scheduled_time
      ,r.metro_id as metro_id
      ,r.id as route_id
      ,r.start_at as route_start_at
      ,r.driver_id as user_id
      ,DATE_ADD(start_at,INTERVAL length minute) AS oncall_end_at
      ,tt.stops
      ,tt.pickups
      ,tt.dropoffs
      ,tt.returns
      ,tt.in_route_moves
      ,tt.first_move_distance
      ,tt.in_routes_distance
      ,tt.pickups_time
      ,tt.dropoffs_time
      ,tt.first_move_time
      ,tt.in_routes_move_time
      ,tt.shipment_company_time
      ,tt.avg_distance
      ,tt.max_distance
      ,tt.min_distance
      ,tt.end_pt
      ,tt.start_pt
      ,r.worked_minutes
    FROM routes r
    LEFT JOIN ${f_tasktemplate.SQL_TABLE_NAME} as tt
      on r.id=tt.route_id
    Where DATE(DATE_ADD(r.start_at,INTERVAL -8 hour)) > '2014-01-01'
    And r.type='Route'
    ) rpmt
 LEFT JOIN ${f_adjustments.SQL_TABLE_NAME} as aj
   on rpmt.route_id=aj.base_route_id
 LEFT JOIN
      (Select
               pes_1.route_id
              ,count(distinct shipment_companyery_id) as shipment_companyeries_count
              ,count(distinct order_id) as orders_count
              ,count(distinct pickup_proxy) as pickup_event_count
          From
            (SELECT
                 d.route_id
                ,d.id as shipment_companyery_id
                ,d.order_id
                ,p1.pickup_group
                ,case when p1.pickup_group IS NOT NULL THEN p1.pickup_group
                     else d.id
                         end pickup_proxy
              FROM shipment_companyeries d
              LEFT JOIN ${grouped_shipment_companyery_summary.SQL_TABLE_NAME} p1
                ON p1.shipment_companyery_id=d.id
            Group By 1,2,3,4,5
            ) pes_1
       Group By 1
      ) pes_2
   on rpmt.route_id = pes_2.route_id
  fields:
  - dimension: pickup_event_count
    type: number
    sql: ${TABLE}.pickup_event_count
  - dimension: orders_count
    type: number
    sql: ${TABLE}.orders_count
  - dimension: base_route_id
    type: int
    primary_key: true
    sql: ${TABLE}.base_route_id
  - dimension: bonus
    type: number
    sql: IFNULL(${driver_payments.bonus_settlement},0)
  - dimension: hour_additions
    type: number
    sql: IFNULL(${TABLE}.hour_additions,0)
  - dimension: mile_additions
    type: number
    sql: IFNULL(${TABLE}.mile_additions,0)
  - dimension: mile_deductions
    type: number
    sql: IFNULL(${TABLE}.mile_deductions,0)
  - dimension: premium_hour_additions
    type: number
    sql: IFNULL(${TABLE}.premium_hour_additions,0)
  - dimension: noshowrate
    type: percentage
    sql: ${driver_time_slots.noshowrate}
  - dimension: scheduled_time
    type: number
    sql: ${TABLE}.scheduled_time
  - dimension: total_scheduled_time_h
    type: sum
    sql: ${TABLE}.scheduled_time/60
  - dimension: min_pmt_qt
    type: number
    sql: ${scheduled_time}/30
  - dimension: min_pmt_min
    type: number
    sql: ${scheduled_time}/2
  - dimension: metro_id
    type: number
    sql: ${TABLE}.metro_id
  - dimension: metros_name_community
    sql: ${metros.name_community}
  - dimension: route_id
    type: number
    sql: ${TABLE}.route_id
  - dimension: user_id
    type: number
    sql: ${TABLE}.user_id
  - dimension: shipment_companyeries
    type: number
    sql: ${billable_tracking_codes_per_route.billable_tracking_code_count}
  - dimension: total_shipment_companyeries
    type: sum
    sql: ${shipment_companyeries}
  - dimension: stops
    type: number
    sql: ${TABLE}.stops
  - dimension: total_stops
    type: sum
    sql: ${TABLE}.stops
  - dimension: pickups
    type: number
    sql: ${TABLE}.pickups
  - dimension: returns
    type: number
    sql: ${TABLE}.returns
  - dimension: avg_stops_per_pickup
    type: number
    sql: ${total_stops}/${total_pickups})
  - dimension: avg_orders_per_pickup
    type: number
    sql: ${total_shipment_companyeries}/${total_pickups}
  - dimension: dropoffs
    type: number
    sql: ${TABLE}.stops
  - dimension: in_route_moves
    type: number
    sql: ${TABLE}.in_route_moves
  - dimension: first_move_distance
    type: number
    sql: ${TABLE}.first_move_distance
  - dimension: in_routes_distance
    type: number
    sql: ${TABLE}.in_routes_distance
  - dimension: miles
    type: number
    sql: ${driver_payments.paid_miles}
  - dimension: pickups_time
    type: number
    sql: ${TABLE}.pickups_time
  - dimension: dropoffs_time
    type: number
    sql: ${TABLE}.dropoffs_time
  - dimension: first_move_time
    type: number
    sql: ${TABLE}.first_move_time
  - dimension: in_routes_move_time
    type: number
    sql: ${TABLE}.in_routes_move_time
  - dimension: avg_distance
    type: number
    sql: ${TABLE}.avg_distance
  - dimension: min_distance
    type: number
    sql: ${TABLE}.min_distance
  - dimension: max_distance
    type: number
    sql: ${TABLE}.max_distance
  - dimension: laststop_distance
    type: number
    sql: ${TABLE}.laststop_distance
  - dimension: worked_time_hrs
    type: number
    sql: (${TABLE}.worked_minutes /60)
  - dimension: actual_shipment_company_time
    type: number
    sql: ${TABLE}.worked_minutes
  - dimension: actual_total_shipment_company_time_h
    type: sum
    sql: ${actual_shipment_company_time}/60
  - dimension: total_paid_time_h
    type: sum
    sql: ${driver_payments.paid_hours}
  - dimension: pmt_distance
    type: number
    sql: ${driver_payments.mileage_settlement}
  - dimension: premiums
    type: number
    sql: IFNULL(${driver_payments.premiums_settlement},0)
  - dimension: time_cost
    type: sum
    sql: ${pmt_time}
  - dimension: gas_cost
    type: sum
    sql: ${pmt_distance}
  - dimension: pdistance_cost
    type: sum
    sql: IFNULL(${bonus_distance},0)
  - dimension: ptime_cost
    type: sum
    sql: IFNULL(${bonus_time},0)
  - dimension: pbonus_cost
    type: sum
    sql: IFNULL(${bonus},0)
  - dimension: driver_utilization
    type: percentage
    sql: ${total_paid_time_h}/${total_scheduled_time_h}
  - dimension: route_utilization
    type: percentage
    sql: ${actual_total_shipment_company_time_h}/${total_paid_time_h}
  - dimension: capacity_utilization
    type: percentage
    sql: ${actual_total_shipment_company_time_h}/${total_scheduled_time_h}
  - dimension: total_pickups
    type: sum
    sql: ${TABLE}.pickups
  - dimension: total_pickups_time
    type: sum
    sql: ${TABLE}.pickups_time
  - dimension: avg_pickup_time_m
    type: number
    sql: ${total_pickups_time}/${total_pickups}
  - dimension: total_dropoffs
    type: sum
    sql: ${TABLE}.dropoffs
  - dimension: total_dropoffs_time
    type: sum
    sql: ${TABLE}.dropoffs_time
  - dimension: avg_dropoff_time_m
    type: number
    sql: ${total_dropoffs_time}/${total_dropoffs}
  - dimension: costpershipment_company
    type: number
    sql: ${total_settlements}/${total_shipment_companyeries}
  - dimension: shipment_companyperhour
    type: number
    sql: ${total_shipment_companyeries}/${actual_total_shipment_company_time_h}
  - dimension: shipment_companyperpaidhour
    type: number
    sql: ${total_shipment_companyeries}/${total_paid_time_h}
  - dimension_group: route_start_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - dow
    sql: ${TABLE}.route_start_at
  - dimension_group: route_start_at_local
    type: time
    timeframes:
    - time
    - date
    - hod
    - week
    - month
    - dow_num
    - year
    sql: case
  when ${metros.id} in (24) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/Hawaii')
  when ${metros.id} in (1,3,4,6,13,18,19,33,34,38,26) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/PACIFIC')
  when ${metros.id} in (2,7,15,16,17,23,35) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/CENTRAL')
  when ${metros.id} in (8,11,20,21,22,22,27,28,29,31,32,36,37,39) then convert_tz(${TABLE}.route_start_at, 'UTC', 'US/Eastern')
end
  - dimension_group: oncall_end_at
    type: time
    timeframes:
    - time
    sql: ${TABLE}.oncall_end_at
  - dimension_group: end_pt
    type: time
    timeframes:
    - time
    - date
  - dimension_group: start_pt
    type: time
    timeframes:
    - time
    - date
  - measure: count
    type: count
  - measure: total_pickup_event_count
    type: sum
    label: total_pickup_event_dontuse
    sql: ${pickup_event_count}
  - measure: total_orders_count
    type: sum
    sql: ${orders_count}
  - measure: total_stop_events
    type: number
    label: total_stop_events_dontuse
    sql: ${total_dropoffs} + ${total_pickup_event_count}
  - measure: orders_per_pickup
    type: number
    sql: ${total_shipment_companyeries} / ${total_pickups}
  - measure: route_count
    type: count_distinct
    sql: ${route_id}
  - measure: shipment_companyroute_count
    type: count_distinct
    filters:
    - shipment_companyeries: not 0
    sql: ${route_id}
  - measure: empty_route_count
    type: number
    sql: ${route_count} - ${shipment_companyroute_count}
  - measure: sum_total_scheduled_time_h
    type: sum
    sql: ${total_scheduled_time_h}
  - measure: average_scheduled_time_per_user
    type: number
    sql: ${total_scheduled_time_h}/${driver_count}
  - measure: orders_per_route
    type: number
    sql: ${total_shipment_companyeries}/${route_count}
  - measure: total_miles
    type: sum
    sql: ${driver_payments.paid_miles}
  - measure: total_worked_time
    type: sum
    sql: ${worked_time_hrs}
  - measure: in_routes_distance_sum
    type: sum
    sql: ${in_routes_distance}
  - measure: total_settlements
    type: sum
    sql: ${driver_payments.total_settlement}
  - measure: total_premiums
    type: sum
    sql: ${premiums}
  - measure: stops_per_paid_hour
    type: number
    sql: ${total_stops}/${total_paid_time_h}
  - measure: driver_count
    type: count_distinct
    sql: ${user_id}
  - measure: averageshipment_companyperuser
    type: number
    sql: ${total_shipment_companyeries}/${driver_count}
  - measure: shipment_companyperroute
    type: number
    sql: ${total_shipment_companyeries}/${shipment_companyroute_count}
  - measure: milesperroute
    type: number
    sql: ${total_miles}/${shipment_companyroute_count}
  - measure: minsperroute
    type: number
    sql: 60*${actual_total_shipment_company_time_h}/${shipment_companyroute_count}
  - measure: milespershipment_company
    type: number
    sql: ${total_miles}/${total_shipment_companyeries}
  - measure: minspershipment_company
    type: number
    sql: 60*${actual_total_shipment_company_time_h}/${total_shipment_companyeries}

f_routespmt.sql
f_routespmt.sql
f_routespmt.view.lkml
f_routespmt.view.lookml

F Routespmt

Blocks

Assumptions

Contents