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

Definitions / estimated_bill

Bill about to be estimated

detector, runs daily 06:00

A meter that has sent no reads for days with its billing close coming up: unless it is read before the close, the customer gets an estimated bill.

metering · Head of Metering · version 2

Each of the last missed_days complete days has no interval reads, and the premise's next billing close (its billing day of this month, or next month once it has passed) is today or within days_to_close days. Started on the first day of the gap.

Latest change: Two silent days, not three: a gap that starts three days before the close was found on the close day itself, too late for a read.

SQL
WITH silent AS (
  SELECT d.meter_id
  FROM meter_daily d
  WHERE d.day < CAST({now} AS DATE) AND d.day >= CAST({now} AS DATE) - {missed_days}
  GROUP BY d.meter_id
  HAVING count(*) = {missed_days} AND max(d.reads) = 0
),
gap AS (
  SELECT s.meter_id,
         coalesce(max(d.day) FILTER (WHERE d.reads > 0) + 1, min(d.day)) AS since
  FROM silent s JOIN meter_daily d ON d.meter_id = s.meter_id AND d.day < CAST({now} AS DATE)
  GROUP BY s.meter_id
),
closing AS (
  SELECT g.meter_id, g.since, p.billing_day, p.account_no, m.premise_id, m.feeder_id, m.serial, m.comms,
         CASE WHEN make_date(year(CAST({now} AS DATE)), month(CAST({now} AS DATE)), p.billing_day) >= CAST({now} AS DATE)
              THEN make_date(year(CAST({now} AS DATE)), month(CAST({now} AS DATE)), p.billing_day)
              ELSE CAST(make_date(year(CAST({now} AS DATE)), month(CAST({now} AS DATE)), p.billing_day) + INTERVAL 1 MONTH AS DATE)
         END AS close_on
  FROM gap g JOIN meters m ON m.id = g.meter_id JOIN premises p ON p.id = m.premise_id
)
SELECT 'meter' AS subject_type, CAST(c.meter_id AS TEXT) AS subject_id, c.meter_id, c.feeder_id, c.premise_id,
       CAST(c.since AS TIMESTAMP) AS started_at, 'low' AS severity,
       json_object(
         'meter_id', c.meter_id,
         'serial', c.serial,
         'comms', c.comms,
         'premise_id', c.premise_id,
         'account_no', c.account_no,
         'feeder_id', c.feeder_id,
         'no_reads_since', c.since,
         'billing_close', c.close_on,
         'days_to_close', date_diff('day', CAST({now} AS DATE), c.close_on)
       ) AS evidence
FROM closing c
WHERE date_diff('day', CAST({now} AS DATE), c.close_on) <= {days_to_close}
ORDER BY started_at, c.meter_id

Tests

meaningtestresultobservedexpected
meteringcount_betweenpass19between 0.0 and 60.0
meteringuniquepass{'duplicates': 0}meter_id unique
meteringdetector_shapepass{'missing_columns': [], 'bad_rows': 0, 'rows': 19}columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object