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: docsActive 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- definitionDocumentation Lead docs v1 First version.