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.

Active shipment

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

How many shipments are active. Operations counts what is still moving; Finance counts what is still open on the books. Neither is made canonical, so every answer shows both, with the shipments in one count and not the other.

opsHead of Operations · ops v1

Booked at or before now, not cancelled at or before now, and not yet delivered as of now (for sea, every container delivered; for air and road, the shipment delivered).

latest change: First version.

  • pass count_betweenobserved 12420, expected between 9000.0 and 15000.0
  • pass uniqueobserved {'duplicates': 0}, expected shipment_id unique
SQL, rendered for now
WITH done AS (
  SELECT s.id FROM shipments s
  JOIN shipment_events e ON e.shipment_id = s.id AND e.type = 'delivered' AND e."at" <= TIMESTAMP 'Tue 18:00:00'
  GROUP BY s.id, s.mode, s.n_containers
  HAVING (s.mode = 'sea' AND count(DISTINCT e.container_id) >= s.n_containers) OR s.mode <> 'sea'
),
cancelled AS (SELECT DISTINCT shipment_id FROM shipment_events WHERE type = 'cancelled' AND "at" <= TIMESTAMP 'Tue 18:00:00')
SELECT s.id AS shipment_id, s.ref, s.mode, s.shipper_id, s.booked_at
FROM shipments s
WHERE s.booked_at <= TIMESTAMP 'Tue 18:00:00'
  AND s.id NOT IN (SELECT id FROM done) AND s.id NOT IN (SELECT shipment_id FROM cancelled)
ORDER BY s.id
financeFinance Controller · finance v1

Booked at or before now, not cancelled, and either not yet invoiced or invoiced and not yet paid as of now. A delivered shipment that is still unpaid counts; a paid shipment still at sea does not.

latest change: First version.

  • pass count_betweenobserved 10493, expected between 5000.0 and 30000.0
  • pass uniqueobserved {'duplicates': 0}, expected shipment_id unique
SQL, rendered for now
WITH cancelled AS (SELECT DISTINCT shipment_id FROM shipment_events WHERE type = 'cancelled' AND "at" <= TIMESTAMP 'Tue 18:00:00')
SELECT s.id AS shipment_id, s.ref, s.mode, s.shipper_id, s.booked_at,
       i.issued_at <= TIMESTAMP 'Tue 18:00:00' AS invoiced, i.amount_usd
FROM shipments s
LEFT JOIN invoices i ON i.shipment_id = s.id
WHERE s.booked_at <= TIMESTAMP 'Tue 18:00:00' AND s.id NOT IN (SELECT shipment_id FROM cancelled)
  AND (i.id IS NULL OR i.issued_at > TIMESTAMP 'Tue 18:00:00' OR i.paid_at IS NULL OR i.paid_at > TIMESTAMP 'Tue 18:00:00')
ORDER BY s.id

History

append-only; nothing is edited in place
  1. definitionFinance Controller finance v1 First version.
  2. definitionHead of Operations ops v1 First version.