Accounts Receivable Aging Report from SQL Server
Build an accounts receivable aging report from SQL Server invoice data: open balances by customer in 0–30, 31–60, 61–90 and 90+ day buckets, plus DSO.
Summary
An accounts receivable aging report lists unpaid invoices by how long they are past due, usually current, 1–30, 31–60, 61–90 and 90+ days, with totals per customer. If invoices live in a SQL Server database, Better Analyst can calculate open balances and buckets with DATEDIFF from the due date and highlight the customers to chase first.
On this page
Who this report is for
Controllers, AR specialists and finance managers at companies whose ERP or billing system runs on Microsoft SQL Server, who need an aging view without exporting to Excel every week.
What the report includes
| Metric | Source field | Why it matters |
|---|---|---|
| Invoice | dbo.Invoices.InvoiceID, InvoiceDate, DueDate (example schema) | Each receivable and the date it falls due. |
| Customer | dbo.Invoices.CustomerID joined to dbo.Customers (example schema) | Who owes the money. |
| Open balance | Calculated: InvoiceAmount − AmountPaid (example schema) | Only unpaid amounts belong in aging. |
| Days past due | Calculated: DATEDIFF(day, DueDate, GETDATE()) | Places each open invoice in a bucket. |
| Aging bucket | Calculated: CASE on days past due (current, 1–30, 31–60, 61–90, 90+) | The standard view for collections and bad-debt reserves. |
| DSO | Calculated: ending AR ÷ credit sales × days in period | Summary of how fast customers pay overall. |
How to build it in Better Analyst from Microsoft SQL Server
- Create a SQL Server login with read-only access (the db_datareader role) to the database that holds invoices and payments.
- In Better Analyst, open Connectors, choose Add Connector, then Microsoft SQL Server, and enter host, port (1433 by default), database, username and password. SQL Server 2016+ and Azure SQL Database are supported.
- Test the connection and save, then describe the invoice and payment tables in Data Context (which columns hold due date, amount and amount paid).
- Ask for an aging report by customer as of today with the five standard buckets and a total row.
Questions to ask
- “Build an AR aging report by customer as of today with current, 1–30, 31–60, 61–90 and 90+ day buckets.”
- “Which accounts have overdue invoices over $10,000?”
- “How has the 90+ day balance changed each month this year?”
- “What was our DSO for each of the last six months?”
Sample output
Sample data for illustration, not real account data.
| Customer | Current | 1–30 | 31–60 | 61–90 | 90+ | Total open |
|---|---|---|---|---|---|---|
| Harbor Foods | $42,300 | $18,900 | $0 | $0 | $0 | $61,200 |
| Northline Retail | $12,800 | $9,400 | $14,200 | $6,100 | $0 | $42,500 |
| Peak Outfitters | $0 | $0 | $3,900 | $8,700 | $21,600 | $34,200 |
| Total | $55,100 | $28,300 | $18,100 | $14,800 | $21,600 | $137,900 |
Charts to use
- Open AR by bucket (Stacked bar chart): Each customer's open balance split into aging buckets, largest first.
- 90+ day balance trend (Line chart): Month-end balance over 90 days past due, the main collections risk.
Keep it up to date
Schedule the aging report every Monday by email to the AR team, and set an alert when the 90+ day total rises above a threshold.
Further reading
Frequently asked questions
Should aging be based on invoice date or due date?
Both are used. Aging by due date shows what is actually overdue and is better for collections; aging by invoice date is common for bad-debt reserves. State which one the report uses.
How do partial payments affect aging?
Only the unpaid remainder belongs in the bucket, so the report must subtract payments applied to each invoice. Credits not yet applied to an invoice are usually shown separately.
Can I build the buckets in Excel instead?
Yes, with a days-past-due column and SUMIFS per bucket. Querying SQL Server directly avoids the weekly export and keeps the report current.