Query · SQL
Customer account statement with running balance
A customer ledger — invoices as charges, payments as credits, and a running balance — so you can print statements and see who owes what.
What people ask
- Print a customer statement showing unpaid sales orders, payments, and balances”
- Show me a customer's account activity with running balance”
- What's the current balance for customers including all invoices and payments?”
The query
customer-statement.sql
WITH ledger AS ( -- Invoices = charges (negative) SELECT c.id customer_id, c.name customer_name, so.num reference, 'Invoice' entry_type, so.dateIssued entry_date, ROUND(-so.totalPrice,2) amount FROM so JOIN customer c ON c.id = so.customerId WHERE so.statusId IN (20,25,60,70) UNION ALL -- Payments = credits (positive) SELECT c.id, c.name, so.num, 'Payment', pt.dateTime, ROUND(pt.amount,2) FROM postransaction pt JOIN so ON so.id = pt.soId JOIN customer c ON c.id = so.customerId ) SELECT customer_name, reference, entry_type, entry_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY entry_date, entry_type, reference) AS running_balance FROM ledger ORDER BY customer_name, entry_date;
Preview · demo data
| Customer | Reference | Type | Date | Amount | Balance |
|---|---|---|---|---|---|
| Acme Bikes | SO-10421 | Invoice | 2026-04-02 | -$1,240.00 | -$1,240.00 |
| Acme Bikes | SO-10421 | Payment | 2026-04-18 | $1,240.00 | $0.00 |
| Acme Bikes | SO-10455 | Invoice | 2026-05-10 | -$880.00 | -$880.00 |
Notes & gotchas
- Invoices are negative (owed), payments positive (received) — a negative running balance means the customer owes you.
- so.totalPrice already includes tax — no need to recompute from line items.
- Status IN (20,25,60,70) = issued / in-progress / fulfilled / closed-short.
- Add HAVING running_balance < 0 for only customers with a balance, or filter to one customer for a printable statement.
Print a customer statement — unpaid sales orders, payments, and a running balance.
Agent Query session
I'll build a ledger that unions sales orders as charges (negative) with payments as credits (positive), then use a window function to carry a running balance per customer.
customer-statement.sql
WITH ledger AS ( -- Invoices = charges (negative) SELECT c.id customer_id, c.name customer_name, so.num reference, 'Invoice' entry_type, so.dateIssued entry_date, ROUND(-so.totalPrice,2) amount FROM so JOIN customer c ON c.id = so.customerId WHERE so.statusId IN (20,25,60,70) UNION ALL -- Payments = credits (positive) SELECT c.id, c.name, so.num, 'Payment', pt.dateTime, ROUND(pt.amount,2) FROM postransaction pt JOIN so ON so.id = pt.soId JOIN customer c ON c.id = so.customerId ) SELECT customer_name, reference, entry_type, entry_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY entry_date, entry_type, reference) AS running_balance FROM ledger ORDER BY customer_name, entry_date;
Agent Query session
Here's a customer's ledger on demo data — a negative balance means they owe you. Add a single-customer filter and it's a printable statement.
| Customer | Reference | Type | Date | Amount | Balance |
|---|---|---|---|---|---|
| Acme Bikes | SO-10421 | Invoice | 2026-04-02 | -$1,240.00 | -$1,240.00 |
| Acme Bikes | SO-10421 | Payment | 2026-04-18 | $1,240.00 | $0.00 |
| Acme Bikes | SO-10455 | Invoice | 2026-05-10 | -$880.00 | -$880.00 |
Want one like this for your Fishbowl?
Send a short brief — an ILC expert builds and tests it, and you get the file ready to import. The quote is free.