Definitions
The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.Free time left
free_time unit: containers canonical: opsContainers waiting at a destination terminal, with the hours of free time they have left (negative once demurrage is running).
opsHead of Operations · ops
v1canonical
Containers discharged at or before now and not gated out at or before now, discharged within 30 days.
latest change: First version.
- pass count_betweenobserved 439, expected between 100.0 and 8000.0
- pass uniqueobserved {'duplicates': 0}, expected container_id unique
SQL, rendered for now
SELECT c.id AS container_id, c.number, s.ref, l.name AS terminal, c.last_free_at,
round(date_diff('minute', TIMESTAMP 'Tue 18:00:00', c.last_free_at) / 60.0, 1) AS hours_left
FROM containers c
JOIN shipments s ON s.id = c.shipment_id
JOIN locations l ON l.id = s.dest_id
WHERE EXISTS (SELECT 1 FROM shipment_events e WHERE e.container_id = c.id AND e.type = 'discharged'
AND e."at" <= TIMESTAMP 'Tue 18:00:00' AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 30 DAY)
AND NOT EXISTS (SELECT 1 FROM shipment_events e WHERE e.container_id = c.id AND e.type = 'gate_out'
AND e."at" <= TIMESTAMP 'Tue 18:00:00')
ORDER BY hours_left, c.id
History
append-only; nothing is edited in place- definitionHead of Operations ops v1 First version.