Definitions
The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.Missed delivery appointment
missed_delivery_appointment unit: appointments operator detector canonical: inlandA delivery appointment at the consignee whose window has passed with no truck arriving, no delivery recorded and no new appointment booked since.
inlandInland Transport Lead · inland
v1canonical
Delivery appointments booked at or before now whose slot ended at least grace_minutes before now (within 7 days), with no arrival and no cancellation at or before now, no 'delivered' event for the same container (or, for air and road, the same shipment) at or before now, and no later delivery appointment for it booked at or before now.
latest change: First version.
- pass count_betweenobserved 0, expected between 0.0 and 150.0
- pass uniqueobserved {'duplicates': 0}, expected appointment_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH due AS (
SELECT a.* FROM trucking_appointments a
WHERE a.kind = 'delivery' AND a.booked_at <= TIMESTAMP 'Tue 18:00:00'
AND a.slot_end <= TIMESTAMP 'Tue 18:00:00' - INTERVAL (60) MINUTE AND a.slot_end >= TIMESTAMP 'Tue 18:00:00' - INTERVAL 7 DAY
AND (a.arrived_at IS NULL OR a.arrived_at > TIMESTAMP 'Tue 18:00:00') AND (a.cancelled_at IS NULL OR a.cancelled_at > TIMESTAMP 'Tue 18:00:00')
)
SELECT 'appointment' AS subject_type, a.id AS subject_id, a.id AS appointment_id, a.shipment_id, a.container_id,
s.shipper_id, a.slot_end AS started_at, 'medium' AS severity,
json_object(
'shipment', s.ref, 'mode', s.mode, 'container', c.number, 'slot_start', a.slot_start,
'slot_end', a.slot_end, 'haulier', h.name, 'haulier_id', a.haulier_id, 'consignee', s.consignee,
'hours_overdue', round(date_diff('minute', a.slot_end, TIMESTAMP 'Tue 18:00:00') / 60.0, 1),
'shipper', sh.name, 'account_owner', sh.account_owner
) AS evidence
FROM due a
JOIN shipments s ON s.id = a.shipment_id
JOIN shippers sh ON sh.id = s.shipper_id
LEFT JOIN containers c ON c.id = a.container_id
LEFT JOIN carriers h ON h.id = a.haulier_id
WHERE NOT EXISTS (SELECT 1 FROM shipment_events e WHERE e.shipment_id = a.shipment_id AND e.type = 'delivered'
AND e."at" <= TIMESTAMP 'Tue 18:00:00' AND e.container_id IS NOT DISTINCT FROM a.container_id)
AND NOT EXISTS (SELECT 1 FROM trucking_appointments b WHERE b.kind = 'delivery' AND b.shipment_id = a.shipment_id
AND b.container_id IS NOT DISTINCT FROM a.container_id AND b.id <> a.id
AND b.booked_at > a.booked_at AND b.booked_at <= TIMESTAMP 'Tue 18:00:00'
AND (b.cancelled_at IS NULL OR b.cancelled_at > TIMESTAMP 'Tue 18:00:00'))
ORDER BY started_at, a.id
Parameters
change one, see what changes in the window, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionInland Transport Lead inland v1 First version.