Lanes freight exceptions
World clock Tue 18:00:00 UTC held · 120×

Definitions

The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.

On-time delivery

on_time_delivery unit: shipments no canonical variant: Ask returns all 2

Deliveries that were on time. Customer Service measures against the date promised to the customer; Carrier Relations measures the carrier against its own schedule. A shipment can pass one and fail the other, so both answers are shown.

customerCustomer Service Lead · cs v1

Shipments whose last delivery in the period (for sea, the last container) was at or before promised_delivery_at.

latest change: First version.

  • pass count_betweenobserved 2554, expected between 500.0 and 6000.0
SQL, rendered for now
WITH delivered AS (
  SELECT e.shipment_id, max(e."at") AS delivered_at, count(DISTINCT e.container_id) AS boxes
  FROM shipment_events e WHERE e.type = 'delivered' AND e."at" <= TIMESTAMP 'Tue 18:00:00' GROUP BY e.shipment_id
)
SELECT s.id AS shipment_id, s.ref, s.mode, d.delivered_at, s.promised_delivery_at
FROM delivered d JOIN shipments s ON s.id = d.shipment_id
WHERE d.delivered_at >= TIMESTAMP 'Tue 18:00:00 (7 days ago)' AND d.delivered_at < TIMESTAMP 'Tue 18:00:00'
  AND (s.mode <> 'sea' OR d.boxes >= s.n_containers)
  AND d.delivered_at <= s.promised_delivery_at
ORDER BY s.id
carrierCarrier Relations Lead · carrier_relations v1

Sea shipments that arrived in the period within carrier_slack_hours of the carrier's scheduled arrival, whatever was promised to the customer.

latest change: First version.

  • pass count_betweenobserved 1745, expected between 300.0 and 6000.0
SQL, rendered for now
SELECT s.id AS shipment_id, s.ref, e."at" AS arrived_at, s.eta_scheduled
FROM shipment_events e JOIN shipments s ON s.id = e.shipment_id
WHERE e.type = 'arrived' AND s.mode = 'sea' AND e."at" <= TIMESTAMP 'Tue 18:00:00'
  AND e."at" >= TIMESTAMP 'Tue 18:00:00 (7 days ago)' AND e."at" < TIMESTAMP 'Tue 18:00:00'
  AND e."at" <= s.eta_scheduled + INTERVAL (24) HOUR
ORDER BY s.id

Parameters

change one, see what changes in the window, then save it as a new version with a note
now 24 · used by carrier

History

append-only; nothing is edited in place
  1. definitionCarrier Relations Lead carrier v1 First version.
  2. definitionCustomer Service Lead customer v1 First version.