SAP Business One Customer Aging Query
SQL queries to calculate customer aging reports with overdue balances broken down by aging buckets (Current, 30, 60, 90, 120+ days).
These are sample queries you can run on Manager Boost. Use them as-is, or edit and customize on our platform. Go custom or use our pre-built queries — you're in control.
Key Concepts
Open Balance Calculation
The open balance is DocTotal - PaidToDate. DocTotal is the invoice total including tax. PaidToDate updates automatically when payments are applied. Always filter DocTotal > PaidToDate to get only unpaid invoices.
Aging Buckets
Aging buckets categorize invoices by how overdue they are. Standard buckets are:
- Current — Not yet due (due date is in the future)
- 1-30 days — Overdue by 1 to 30 days
- 31-60 days — Overdue by 31 to 60 days
- 61-90 days — Overdue by 61 to 90 days
- 91-120 days — Overdue by 91 to 120 days
- 120+ days — Overdue by more than 120 days
Days Overdue Calculation
Use DATEDIFF(DAY, DocDueDate, GETDATE()) to calculate days overdue. If the due date is in the future, this returns a negative number — handle this in your CASE statement.
DocStatus vs CANCELED
DocStatus = 'O' means the document is open (not fully paid). CANCELED = 'N' means it hasn't been cancelled. Always filter both: DocStatus = 'O' AND CANCELED = 'N' to get valid open invoices.
Available Fields Reference
OINV (A/R Invoice Header)
DocEntry DocNum DocDate DocDueDate CardCode CardName DocTotal PaidToDate DocStatus CANCELED SlpCode
OCRD (Customer Master)
CardCode CardName CreditLine Balance Phone1 E_Mail
Aging Summary for a Customer
Get aging buckets for a specific customer. This query calculates the open balance and categorizes it into aging buckets based on days overdue.
SELECT
T0.CardCode,
T0.CardName,
SUM(T0.DocTotal - T0.PaidToDate) AS TotalBalance,
SUM(CASE
WHEN T0.DocDueDate >= GETDATE() THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Current,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 1 AND 30
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days30,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 31 AND 60
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days60,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 61 AND 90
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days90,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) > 90
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days120Plus
FROM OINV T0
WHERE T0.CardCode = 'C10000'
AND T0.DocStatus = 'O'
AND T0.CANCELED = 'N'
AND T0.DocTotal > T0.PaidToDate
GROUP BY T0.CardCode, T0.CardName| CardCode | CardName | TotalBalance | Current | Days30 | Days60 | Days90 | Days120Plus |
|---|---|---|---|---|---|---|---|
| C10000 | Acme Corp | 15250.00 | 5250.00 | 3000.00 | 4000.00 | 2000.00 | 1000.00 |
Aging Report for All Customers
Generate a complete aging report with totals by customer. Join OCRD to get customer details and credit limits for context.
SELECT
T0.CardCode,
T0.CardName,
T1.CreditLine,
SUM(T0.DocTotal - T0.PaidToDate) AS TotalBalance,
SUM(CASE
WHEN T0.DocDueDate >= GETDATE() THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Current,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 1 AND 30
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days30,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 31 AND 60
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days60,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 61 AND 90
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days90,
SUM(CASE
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) > 90
THEN T0.DocTotal - T0.PaidToDate
ELSE 0
END) AS Days120Plus
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocStatus = 'O'
AND T0.CANCELED = 'N'
AND T0.DocTotal > T0.PaidToDate
AND T1.CardType = 'C'
GROUP BY T0.CardCode, T0.CardName, T1.CreditLine
HAVING SUM(T0.DocTotal - T0.PaidToDate) > 0
ORDER BY TotalBalance DESC| CardCode | CardName | TotalBalance | Current | Days30 | Days60 | Days90 | Days120Plus |
|---|---|---|---|---|---|---|---|
| C10000 | Acme Corp | 15250.00 | 5250.00 | 3000.00 | 4000.00 | 2000.00 | 1000.00 |
| C10045 | Global Supplies | 8750.00 | 0.00 | 0.00 | 5000.00 | 3750.00 | 0.00 |
Detailed Overdue Invoices
List individual overdue invoices with aging bucket and days overdue. Useful for collections follow-up.
SELECT
T0.CardCode,
T0.CardName,
T0.DocNum,
T0.DocDate,
T0.DocDueDate,
T0.DocTotal - T0.PaidToDate AS BalanceDue,
DATEDIFF(DAY, T0.DocDueDate, GETDATE()) AS DaysOverdue,
CASE
WHEN T0.DocDueDate >= GETDATE() THEN 'Current'
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 1 AND 30 THEN '1-30 Days'
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 31 AND 60 THEN '31-60 Days'
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 61 AND 90 THEN '61-90 Days'
WHEN DATEDIFF(DAY, T0.DocDueDate, GETDATE()) BETWEEN 91 AND 120 THEN '91-120 Days'
ELSE '120+ Days'
END AS AgingBucket
FROM OINV T0
WHERE T0.DocStatus = 'O'
AND T0.CANCELED = 'N'
AND T0.DocTotal > T0.PaidToDate
AND T0.DocDueDate < GETDATE()
ORDER BY T0.CardCode, DaysOverdue DESC| CardCode | CardName | DocNum | DocDueDate | BalanceDue | DaysOverdue | AgingBucket |
|---|---|---|---|---|---|---|
| C10000 | Acme Corp | 12345 | 2024-01-15 | 5250.00 | 28 | 1-30 Days |
| C10000 | Acme Corp | 12350 | 2023-12-01 | 4000.00 | 73 | 61-90 Days |
Tip: Filter DocDueDate < GETDATE() to show only overdue invoices, or remove it to include current invoices.