Feeder Vellmoor Distribution · control room Synthetic data Sun 02:00

Definitions / crew_to_restored_area

Crew sent to a restored area

detector, runs realtime

A crew still on the road to a fault whose customers already have supply back: the ticket's meters have sent their restores, so the crew is driving to nothing while other faults wait.

control · Head of Network Operations · version 2

Fault work orders whose crew was dispatched and has not arrived as of now, and has not been stood down. Of the meters on the ticket's transformers that sent a last gasp in the two hours before the ticket opened (or since), at least min_restored_share have sent a power restore after it, the last one at least settle_minutes ago, and at least min_meters gasped.

Latest change: Eight in ten restores (not nine) and three minutes' settling: one restore message in twelve never arrives, so nine in ten missed crews that were plainly driving to supply already back.

SQL
WITH moving AS (
  SELECT w.id AS work_order_id, w.crew_id, w.outage_id, w.feeder_id, w.dispatched_at, w.arrived_at
  FROM work_orders w
  WHERE w.kind = 'fault' AND w.outage_id IS NOT NULL
    AND w.dispatched_at <= {now} AND (w.arrived_at IS NULL OR w.arrived_at > {now})
    AND (SELECT count(*) FILTER (WHERE e.type = 'stood_down') - count(*) FILTER (WHERE e.type = 'stood_down_reverted')
         FROM crew_events e WHERE e.work_order_id = w.id AND e."at" <= {now}) = 0
),
scope AS (
  SELECT mv.work_order_id, m.id AS meter_id, o.opened_at
  FROM moving mv
  JOIN outages o ON o.id = mv.outage_id
  JOIN transformers t ON t.feeder_id = o.feeder_id
   AND (o.transformer_id = t.id OR (o.transformer_id IS NULL AND t.position >= o.from_position))
  JOIN meters m ON m.transformer_id = t.id
),
seen AS (
  SELECT s.work_order_id, s.meter_id, max(g."at") AS gasp_at
  FROM scope s
  JOIN meter_events g ON g.meter_id = s.meter_id AND g.type = 'last_gasp'
   AND g."at" >= s.opened_at - INTERVAL 2 HOUR AND g."at" <= {now}
  GROUP BY s.work_order_id, s.meter_id
),
back AS (
  SELECT s.work_order_id, s.meter_id, max(b."at") AS back_at
  FROM seen s
  JOIN meter_events b ON b.meter_id = s.meter_id AND b.type = 'power_restore' AND b."at" > s.gasp_at AND b."at" <= {now}
  GROUP BY s.work_order_id, s.meter_id
),
share AS (
  SELECT s.work_order_id, count(*) AS gasped, count(b.meter_id) AS restored, max(b.back_at) AS last_restore
  FROM seen s LEFT JOIN back b ON b.work_order_id = s.work_order_id AND b.meter_id = s.meter_id
  GROUP BY s.work_order_id
)
SELECT 'work_order' AS subject_type, CAST(mv.work_order_id AS TEXT) AS subject_id, mv.work_order_id, mv.crew_id,
       mv.outage_id, mv.feeder_id, sh.last_restore AS started_at, 'medium' AS severity,
       json_object(
         'work_order_id', mv.work_order_id,
         'crew_id', mv.crew_id,
         'crew', c.call_sign,
         'outage_id', mv.outage_id,
         'feeder_id', mv.feeder_id,
         'dispatched_at', mv.dispatched_at,
         'meters_gasped', sh.gasped,
         'meters_restored', sh.restored,
         'restored_share', round(sh.restored / sh.gasped, 3),
         'last_restore_at', sh.last_restore,
         'minutes_since_restore', round(date_diff('second', sh.last_restore, {now}) / 60.0, 1)
       ) AS evidence
FROM moving mv
JOIN share sh ON sh.work_order_id = mv.work_order_id
JOIN crews c ON c.id = mv.crew_id
WHERE sh.gasped >= {min_meters} AND sh.restored >= {min_restored_share} * sh.gasped
  AND sh.last_restore <= {now} - INTERVAL ({settle_minutes}) MINUTE
ORDER BY started_at, mv.work_order_id

Tests

meaningtestresultobservedexpected
controlcount_betweenpass0between 0.0 and 10.0
controluniquepass{'duplicates': 0}work_order_id unique
controldetector_shapepass{'missing_columns': [], 'bad_rows': 0, 'rows': 0}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object