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.

Free time left

free_time unit: containers canonical: ops

Containers 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
  1. definitionHead of Operations ops v1 First version.