Definitions / planned_work_collision
Planned work colliding with a fault
detector, runs realtimeBooked planned work starting soon on a feeder that has an open fault, or whose tie feeder (its backup supply) has one, or that is itself the backup for a faulted feeder. Taking it out would leave customers with no way back.
planning · Head of Network Planning · version 1
Planned outages not cancelled, starting after now and within lookahead_hours, where an open unplanned ticket covering a whole feeder or a section of one sits on the work's feeder, on its tie feeder, or on a feeder whose tie is the work's feeder. The agent's own tickets count for agent_ticket_hold_hours.
Latest change: First version.
SQL
WITH faults AS (
SELECT o.id AS outage_id, o.feeder_id, o.opened_at, f.tie_feeder_id
FROM outages o JOIN feeders f ON f.id = o.feeder_id
WHERE o.transformer_id IS NULL AND o.source <> 'planned' AND coalesce(o.status, '') <> 'withdrawn'
AND o.opened_at <= {now} AND (o.restored_at IS NULL OR o.restored_at > {now})
AND (o.source <> 'agent' OR o.opened_at > {now} - INTERVAL ({agent_ticket_hold_hours}) HOUR)
),
works AS (
SELECT p.*, f.tie_feeder_id AS work_tie
FROM planned_outages p JOIN feeders f ON f.id = p.feeder_id
WHERE p.cancelled_at IS NULL AND p.start_at > {now} AND p.start_at <= {now} + INTERVAL ({lookahead_hours}) HOUR
AND p.notified_at <= {now}
),
clash AS (
SELECT w.id AS planned_outage_id, w.feeder_id, w.start_at, w.end_at, w.reason,
min(fa.opened_at) AS fault_opened_at, list(DISTINCT fa.outage_id) AS outage_ids,
list(DISTINCT fa.feeder_id) AS fault_feeder_ids,
bool_or(fa.feeder_id = w.feeder_id) AS same_feeder
FROM works w JOIN faults fa
ON fa.feeder_id = w.feeder_id OR fa.feeder_id = w.work_tie OR fa.tie_feeder_id = w.feeder_id
GROUP BY w.id, w.feeder_id, w.start_at, w.end_at, w.reason
)
SELECT 'planned_work' AS subject_type, CAST(c.planned_outage_id AS TEXT) AS subject_id, c.planned_outage_id,
c.feeder_id, greatest(c.fault_opened_at, c.start_at - INTERVAL ({lookahead_hours}) HOUR) AS started_at,
'high' AS severity,
json_object(
'planned_outage_id', c.planned_outage_id,
'feeder_id', c.feeder_id,
'work_starts_at', c.start_at,
'work_ends_at', c.end_at,
'reason', c.reason,
'fault_outage_ids', c.outage_ids,
'fault_feeder_ids', c.fault_feeder_ids,
'same_feeder', c.same_feeder,
'hours_to_start', round(date_diff('minute', {now}, c.start_at) / 60.0, 1)
) AS evidence
FROM clash c
ORDER BY started_at, c.planned_outage_id
Tests
| meaning | test | result | observed | expected |
|---|---|---|---|---|
| planning | count_between | pass | 0 | between 0.0 and 10.0 |
| planning | unique | pass | {'duplicates': 0} | planned_outage_id unique |
| planning | detector_shape | pass | {'missing_columns': [], 'bad_rows': 0, 'rows': 0} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |