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.

Document complete

document_complete unit: shipments canonical: docs

Active shipments with every document they need on file: commercial invoice, packing list, the transport document for their mode, a VGM declaration at sea, and the certificates their destination and goods require.

docsDocumentation Lead · docs v1canonical

Shipments that have departed at or before now and not arrived, with every expected document received at or before now and not rejected as of now.

latest change: First version.

  • pass count_betweenobserved 5855, expected between 1000.0 and 12000.0
SQL, rendered for now
WITH moving AS (
  SELECT s.id, s.ref, s.mode FROM shipments s
  WHERE EXISTS (SELECT 1 FROM shipment_events e WHERE e.shipment_id = s.id AND e.type = 'departed' AND e."at" <= TIMESTAMP 'Tue 18:00:00')
    AND NOT EXISTS (SELECT 1 FROM shipment_events e WHERE e.shipment_id = s.id AND e.type = 'arrived' AND e."at" <= TIMESTAMP 'Tue 18:00:00')
),
expected AS (
  SELECT d.shipment_id, count(DISTINCT d.doc_type) AS expected,
         count(DISTINCT d.doc_type) FILTER (WHERE d.received_at <= TIMESTAMP 'Tue 18:00:00'
                                            AND (d.rejected_at IS NULL OR d.rejected_at > TIMESTAMP 'Tue 18:00:00')) AS on_file
  FROM documents d WHERE d.shipment_id IN (SELECT id FROM moving) GROUP BY d.shipment_id
)
SELECT m.id AS shipment_id, m.ref, m.mode, x.expected, x.on_file
FROM moving m JOIN expected x ON x.shipment_id = m.id
WHERE x.on_file >= x.expected
ORDER BY m.id

History

append-only; nothing is edited in place
  1. definitionDocumentation Lead docs v1 First version.