Cost Calculation

Cost Calculation (Vehicles & Staff)

The problem

Vehicle and staff costs cannot be calculated directly from the orders table. The reason is a mismatch between how costs and orders are structured:

Costs are daily: each vehicle and each staff member has a daily_rate, meaning the cost is per day of use, regardless of how many orders they handle.
Orders reference vehicles and staff per order: in the orders table, we see which van and which driver handled each order, but not which ones were active on a given day.

This means we cannot simply sum costs per order — a van used for 50 orders in one day still costs one daily_rate, not 50x the rate. The same applies to staff.

The approach

To solve this, the cost calculation is done in two steps for both vehicles and staff:

Step 1 — Aggregate by day: build an intermediate table that lists the distinct vehicles (or staff) used per day from the orders/routes data. This gives us a daily view of who and what was active.

Step 2 — Join on daily_rate: join this daily usage table with the vehicles_price or staff_price table to get the actual cost per entity per day.

This way, each vehicle or staff member is counted once per day, and its daily_rate is applied only once regardless of the number of orders handled.

`daily_orders_vehicles` query

Step 1 — Aggregate vehicles by day: group orders by orderDate + vehicleId to get one row per vehicle per day, with the number of orders handled.

WITH orders_per_driver AS (
  SELECT
    drv.customerName,
    dov.orderDate,
    drv.vehicleId,
    drv.vehicleName,
    MAX(drv.delivery_count) AS nb_orders
  FROM `dingoo-prod.business.daily_orders_view` AS dov
  JOIN `dingoo-prod.business.daily_routes_view` AS drv
    ON drv.routeId = dov.route
  GROUP BY dov.orderDate, drv.vehicleName, drv.vehicleId, drv.customerName
)

Step 2 — Join on vehicles_price to get the daily_rate for each vehicle.

SELECT
  o.customerName,
  o.orderDate,
  o.vehicleName,
  o.nb_orders as order_count,
  sp.daily_rate as vehicles_cost
FROM orders_per_driver AS o
LEFT JOIN `dingoo-dev.reporting.vehicles_price` AS sp
  ON o.vehicleId = sp.mongoId

`vehicles_price` table schema

Field	Type	Description
`mongoId`	STRING	MongoDB document ID
`id`	STRING	Vehicle identifier
`name`	STRING	Vehicle name
`daily_rate`	NUMERIC	Cost per day of use
`customer`	STRING	Customer name
`businessId`	STRING	Business reference
`contractId`	STRING	Contract reference
`brand`	STRING	Vehicle brand
`model`	STRING	Vehicle model
`year`	STRING	Vehicle year
`color`	STRING	Vehicle color
`type`	STRING	Vehicle type
`licensePlate`	STRING	License plate
`isActive`	STRING	Whether the vehicle is active
`archived`	STRING	Whether the vehicle is archived
`capacity`	STRING	Vehicle capacity

`daily_orders_staff` query

Step 1 — Aggregate staff by day: group orders by orderDate + driverId to get one row per driver per day, with the number of orders handled.

WITH orders_per_driver AS (
  SELECT
    drv.customerName,
    dov.orderDate,
    drv.driverId,
    drv.driverName,
    COUNT(drv.delivery_count) AS nb_orders
  FROM `dingoo-prod.business.daily_orders_view` AS dov
  JOIN `dingoo-prod.business.daily_routes_view` AS drv
    ON drv.routeId = dov.route
  GROUP BY dov.orderDate, drv.driverId, drv.driverName, drv.customerName
)

Step 2 — Join on staff_price to get the daily_rate for each driver.

SELECT
  o.customerName,
  o.orderDate,
  o.driverName,
  o.nb_orders as order_count,
  sp.daily_rate as staff_cost
FROM orders_per_driver AS o
LEFT JOIN `dingoo-dev.reporting.staff_price` AS sp
  ON o.driverId = sp.mongoId
ORDER BY o.orderDate, o.driverName;

`staff_price` table schema

Field	Type	Description
`name`	STRING	Staff member name
`email`	STRING	Staff member email
`mongoId`	STRING	MongoDB document ID
`archived`	STRING	Whether the staff member is archived
`isActive`	STRING	Whether the staff member is active
`isConfirmed`	STRING	Whether the staff member is confirmed
`daily_rate`	NUMERIC	Cost per day of work

Cost Calculation