Keysproperty operations Corbelstone Residential

Arrears

Which tenants are in arrears. Credit control and finance count differently: credit control counts rent unpaid for arrears_days or more (the point where the reminder schedule starts); finance counts any rent unpaid after its due date, because it is already money the company does not have. Until one is made canonical, every answer shows both.

Definition
arrears

Params: arrears_days = 10 · evaluated at Mon 20:00 · two meanings, no canonical one: both are shown

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

finance · v1 · Finance Controller

1,424

leases at Mon 20:00, value 1786079.85

Leases with any rent charge past its due date and not covered by payments, however late.

finance · changed 2026-11-01: First version.

Tests

  • count_between min 20.0 max 3000.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 AND c.due_date < {now}
GROUP BY c.lease_id
ORDER BY c.lease_id