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 expiring

free_time_expiring unit: containers operator detector canonical: ops

A container that is available at the destination terminal (discharged, cleared by customs, bill of lading released), has not left the terminal, has no pickup booked, and whose free time ends within the warning horizon (or has already ended). After free time the terminal charges demurrage every day.

opsHead of Operations · ops v1canonical

Available (an 'available' event at or before now, within lookback_days), no gate-out at or before now, no pickup appointment booked at or before now and not cancelled, and last_free_at minus warn_hours at or before now. The evidence counts the terminal's pickup slots that still have room between two hours from now and the end of free time, as of now.

latest change: First version.

  • pass count_betweenobserved 6, expected between 0.0 and 400.0
  • pass uniqueobserved {'duplicates': 0}, expected container_id unique
  • pass tuesday_morningobserved 1, expected between 1.0 and 200.0
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 6}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH avail AS (
  SELECT e.container_id, min(e."at") AS available_at
  FROM shipment_events e
  WHERE e.type = 'available' AND e.container_id IS NOT NULL AND e."at" <= TIMESTAMP 'Tue 18:00:00'
    AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL (30) DAY
  GROUP BY e.container_id
),
gone AS (
  SELECT DISTINCT e.container_id FROM shipment_events e
  WHERE e.type = 'gate_out' AND e."at" <= TIMESTAMP 'Tue 18:00:00' AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL (30) DAY
),
booked AS (
  SELECT DISTINCT a.container_id FROM trucking_appointments a
  WHERE a.kind = 'pickup' AND a.booked_at <= TIMESTAMP 'Tue 18:00:00' AND (a.cancelled_at IS NULL OR a.cancelled_at > TIMESTAMP 'Tue 18:00:00')
),
risk AS (
  SELECT c.id AS container_id, c.number, c.equipment, c.last_free_at, s.id AS shipment_id, s.ref, s.shipper_id,
         s.dest_id, v.available_at
  FROM avail v
  JOIN containers c ON c.id = v.container_id
  JOIN shipments s ON s.id = c.shipment_id
  LEFT JOIN gone g ON g.container_id = c.id
  LEFT JOIN booked b ON b.container_id = c.id
  WHERE g.container_id IS NULL AND b.container_id IS NULL
    AND c.last_free_at - INTERVAL (48) HOUR <= TIMESTAMP 'Tue 18:00:00'
),
room AS (
  SELECT t.location_id, t.slot_start, t.slot_end, t.capacity - count(a.id) AS free
  FROM terminal_slots t
  LEFT JOIN trucking_appointments a ON a.kind = 'pickup' AND a.location_id = t.location_id
    AND a.slot_start = t.slot_start AND a.booked_at <= TIMESTAMP 'Tue 18:00:00' AND (a.cancelled_at IS NULL OR a.cancelled_at > TIMESTAMP 'Tue 18:00:00')
  WHERE t.location_id IN (SELECT dest_id FROM risk)
    AND t.slot_start >= TIMESTAMP 'Tue 18:00:00' + INTERVAL 2 HOUR AND t.slot_start < TIMESTAMP 'Tue 18:00:00' + INTERVAL (48 + 24) HOUR
  GROUP BY t.location_id, t.slot_start, t.slot_end, t.capacity
)
SELECT 'container' AS subject_type, r.container_id AS subject_id, r.container_id, r.shipment_id, r.shipper_id,
       greatest(r.available_at, r.last_free_at - INTERVAL (48) HOUR) AS started_at,
       CASE WHEN r.last_free_at <= TIMESTAMP 'Tue 18:00:00' THEN 'critical' ELSE 'high' END AS severity,
       json_object(
         'container', r.number, 'shipment', r.ref, 'equipment', r.equipment,
         'terminal_id', r.dest_id, 'terminal', l.name,
         'available_at', r.available_at, 'last_free_at', r.last_free_at,
         'hours_left', round(date_diff('minute', TIMESTAMP 'Tue 18:00:00', r.last_free_at) / 60.0, 1),
         'slots_with_room', (SELECT count(*) FROM room m WHERE m.location_id = r.dest_id AND m.free > 0
                             AND m.slot_end <= r.last_free_at),
         'earliest_slot_with_room', (SELECT min(m.slot_start) FROM room m WHERE m.location_id = r.dest_id
                                     AND m.free > 0),
         'shipper', sh.name, 'account_owner', sh.account_owner
       ) AS evidence
FROM risk r
JOIN locations l ON l.id = r.dest_id
JOIN shippers sh ON sh.id = r.shipper_id
ORDER BY started_at, r.container_id

Parameters

change one, see what changes in the window, then save it as a new version with a note
now 48 · used by ops
now 30 · used by ops

History

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