Definitions / estimated_bill
Bill about to be estimated
detector, runs daily 06:00A 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
| meaning | test | result | observed | expected |
|---|---|---|---|---|
| metering | count_between | pass | 19 | between 0.0 and 60.0 |
| metering | unique | pass | {'duplicates': 0} | meter_id unique |
| metering | detector_shape | pass | {'missing_columns': [], 'bad_rows': 0, 'rows': 19} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |