All examples
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.

Type: Query Output: TableData: Demo sandbox

What people ask

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

3 rows · demo data
CustomerReferenceTypeDateAmountBalance
Acme BikesSO-10421Invoice2026-04-02-$1,240.00-$1,240.00
Acme BikesSO-10421Payment2026-04-18$1,240.00$0.00
Acme BikesSO-10455Invoice2026-05-10-$880.00-$880.00

Notes & gotchas

A locked session — the real conversation that produced this report. Open it in the app to fork and continue.
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.

preview · demo data
CustomerReferenceTypeDateAmountBalance
Acme BikesSO-10421Invoice2026-04-02-$1,240.00-$1,240.00
Acme BikesSO-10421Payment2026-04-18$1,240.00$0.00
Acme BikesSO-10455Invoice2026-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.

Related examples