credit_control · v1 · Head of Credit Control
63
leases at Mon 20:00, value 208987.06
Leases whose oldest unpaid charge (first in, first out) is at least arrears_days past its due date.
credit control · changed 2026-11-01: First version.
Tests
- count_between min 20.0 max 600.0
- unique on lease_id
SQL
WITH charges AS (
SELECT lease_id, due_date,
sum(amount) OVER (PARTITION BY lease_id ORDER BY due_date, id ROWS UNBOUNDED PRECEDING) AS cum_charged
FROM rent_ledger WHERE kind = 'charge' AND posted_at <= {now}
),
paid AS (SELECT lease_id, -sum(amount) AS paid FROM rent_ledger
WHERE kind IN ('payment', 'concession') AND posted_at <= {now} GROUP BY lease_id)
SELECT c.lease_id, any_value(l.building_id) AS building_id, min(c.due_date) AS oldest_due,
round(max(c.cum_charged) - coalesce(any_value(p.paid), 0), 2) AS balance
FROM charges c LEFT JOIN paid p USING (lease_id) JOIN leases l ON l.id = c.lease_id
WHERE c.cum_charged > coalesce(p.paid, 0) + 0.005
GROUP BY c.lease_id
HAVING min(c.due_date) <= {now} - INTERVAL ({arrears_days}) DAY
ORDER BY c.lease_id