Accounts Receivable and Aging Analysis

Accounts Receivable (AR) aging is the most important report in cash flow management. It answers: "Of the money owed to us, how old is each unpaid invoice?" An invoice that's 1 day overdue is very different from one that's 90+ days overdue — the older it is, the lower the probability of collection, and the more urgent the intervention.

Every accounting system (QuickBooks, Xero, SAP) generates an aging report. This lesson shows you how to build one from raw data in SQL.

What "Aging" Means

Aging groups unpaid invoices by how many days past their due date they are:

The older the bucket, the lower the expected collection rate. Finance teams use aging reports daily to prioritise collection efforts.

Computing Days Outstanding

The first step is calculating how many days each unpaid invoice has been outstanding. CURRENT_DATE - due_date gives the number of days since the due date — positive means overdue, negative means not yet due.

We use GREATEST(..., 0) to treat future-due invoices as 0 days overdue (they're current, not actually overdue). The LEFT JOIN payments ensures we include invoices with no payments at all.

WITH invoice_outstanding AS (
    SELECT
        i.invoice_id,
        i.client_id,
        i.invoice_no,
        i.issue_date,
        i.due_date,
        (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id),
        COALESCE(SUM(p.amount), 0) AS amount,
        (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id) - COALESCE(SUM(p.amount), 0) AS outstanding,
        GREATEST(CURRENT_DATE - i.due_date, 0) AS days_overdue
    FROM invoices i
    LEFT JOIN payments p ON p.invoice_id = i.invoice_id
    WHERE i.status NOT IN ('paid', 'cancelled')
    GROUP BY i.invoice_id, i.client_id, i.invoice_no, i.issue_date, i.due_date, (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id)
    HAVING (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id) - COALESCE(SUM(p.amount), 0) > 0
)
SELECT
    invoice_no, issue_date, due_date,
    outstanding, days_overdue,
    CASE
        WHEN days_overdue = 0 THEN 'Current'
        WHEN days_overdue <= 30 THEN '1-30 days'
        WHEN days_overdue <= 60 THEN '31-60 days'
        WHEN days_overdue <= 90 THEN '61-90 days'
        ELSE '90+ days'
    END AS aging_bucket
FROM invoice_outstanding
ORDER BY days_overdue DESC;

What This Returns

Each row is one unpaid (or partially paid) invoice with its outstanding balance and aging bucket. The HAVING clause filters out invoices where payments have fully covered the invoice — even if status wasn't updated. This catches partially paid invoices correctly regardless of the status field.