Downloads

SQL for accountants

Accounting questions answered in SQL and checked on real sample data: aging, trial balance, year over year, duplicates, accruals and fund balances.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Answer aging, trial balance and year-over-year questions in SQL.
  • Find duplicate records and check that a three-way match holds.
  • Build accruals by month and fund balances with the right join pattern.
  • Avoid double counting after joins, and check each query against a control total.

Accountants ask the same questions every close: who owes us money, does the trial balance tie, did we bill what we delivered, did anything post twice. SQL answers those questions directly, and the answers can be checked. This guide gives you the queries a controller, auditor or finance analyst uses most. Each one was run against the LearnSQL sample databases before it appeared here.

Every query is a read-only SELECT in SQL Server (T-SQL), with MariaDB notes where the syntax changes. New to SQL? Start with SQL essentials. The window functions are explained in SQL deep end.

The sample databases

Database What it is Tables used here
classicmodels A model-car wholesaler customers, orders, orderdetails
cpafirm A CPA firm: engagements, time, invoices, payments, a general ledger invoices, invoice_lines, payments, payment_applications, invoice_adjustments, engagements, time_entries, gl_journal_lines, gl_journal_entries, gl_accounts, dataset_info
nonprofit A regional nonprofit with fund accounting gl_journal_lines, gl_journal_entries, gl_accounts, funds, grants

cpafirm and nonprofit tables sit in the dbo schema, so invoices and dbo.invoices are the same. classicmodels uses its database name as the schema. In MariaDB, drop every dbo. prefix.

Dates. classicmodels dates shift every day, so never hard-code them. cpafirm and nonprofit have a fixed as-of date, 2026-10-24, in dataset_info. Count the rows a total comes from, then tie it to a second number. A total you have not tied is an opinion.

Sampling and audit trails

A systematic sample takes a fixed slice of rows, so anyone can rerun it and get the same entries. This takes entries whose number leaves a remainder of 7 when divided by 1000, about 0.1 percent:

SELECT journal_entry_id, entry_date, source_type, memo
FROM dbo.gl_journal_entries
WHERE journal_entry_id % 1000 = 7
ORDER BY journal_entry_id;

A random sample (ORDER BY NEWID()) differs on every run, so save the result. An audit trail rebuilds a posting from its date, source, poster and memo. In nonprofit, the monthly entries and one opening entry lack a source ID:

SELECT source_type, COUNT(*) AS entries, COUNT(DISTINCT posted_by) AS posters,
       SUM(CASE WHEN source_id IS NULL THEN 1 ELSE 0 END) AS no_source_id
FROM dbo.gl_journal_entries
GROUP BY source_type
ORDER BY entries DESC;

First control: every entry balances. Run this before any other ledger test. In nonprofit it checks 34,377 entries and finds none unbalanced.

SELECT COUNT(*) AS entries_checked,
       SUM(CASE WHEN x.dr <> x.cr THEN 1 ELSE 0 END) AS unbalanced
FROM (SELECT e.journal_entry_id, SUM(l.debit) AS dr, SUM(l.credit) AS cr
      FROM dbo.gl_journal_entries AS e
      JOIN dbo.gl_journal_lines AS l ON l.journal_entry_id = e.journal_entry_id
      GROUP BY e.journal_entry_id) AS x;

1. A/R aging buckets

Question. How much do clients owe, and how old is it? Build each open balance from the base tables (invoice total, less payments applied, less adjustments such as write-offs), then bucket it by days past the as-of date.

WITH paid AS (
    SELECT invoice_id, SUM(amount_applied) AS paid_amount
    FROM payment_applications GROUP BY invoice_id
), adj AS (
    SELECT invoice_id, SUM(amount) AS adjusted_amount
    FROM invoice_adjustments GROUP BY invoice_id
), open_items AS (
    SELECT i.invoice_id, i.due_date,
           i.total_amount - ISNULL(p.paid_amount, 0) - ISNULL(a.adjusted_amount, 0) AS open_amount
    FROM invoices AS i
    LEFT JOIN paid AS p ON p.invoice_id = i.invoice_id
    LEFT JOIN adj AS a  ON a.invoice_id = i.invoice_id
), aged AS (
    SELECT open_amount,
           DATEDIFF(day, due_date, (SELECT as_of_date FROM dataset_info)) AS days_past_due
    FROM open_items WHERE open_amount > 0.005
)
SELECT SUM(CASE WHEN days_past_due <= 0 THEN open_amount ELSE 0 END) AS current_amt,
       SUM(CASE WHEN days_past_due BETWEEN 1 AND 30 THEN open_amount ELSE 0 END) AS d1_30,
       SUM(CASE WHEN days_past_due BETWEEN 31 AND 60 THEN open_amount ELSE 0 END) AS d31_60,
       SUM(CASE WHEN days_past_due BETWEEN 61 AND 90 THEN open_amount ELSE 0 END) AS d61_90,
       SUM(CASE WHEN days_past_due > 90 THEN open_amount ELSE 0 END) AS over_90,
       SUM(open_amount) AS total_open, COUNT(*) AS open_invoices
FROM aged;

What it returns. One row. Most of the open balance is current, and the over-90 bucket is a smaller but real share. The open total is about 566 thousand dollars across 658 invoices.

Control. The buckets sum to the total in vw_ar_aging, and the open total matches vw_invoice_balances.

MariaDB. IFNULL for ISNULL, and DATEDIFF((SELECT as_of_date FROM dataset_info), due_date), because MariaDB's DATEDIFF takes two dates and no unit.

Try it

Clients owing more than 1,000 past 90 days

Which clients have more than 1,000 that is over 90 days past due? Show the client name and the amount over 90 days, largest first.

Runs on cpafirm (SQL Server). Only read-only SELECT queries run here.

2. Trial balance

Question. Do the balances net to zero, and what is each balance at the as-of date?

SELECT a.account_number, a.account_name, a.account_type,
       SUM(l.debit) AS debits, SUM(l.credit) AS credits, SUM(l.debit - l.credit) AS net
FROM gl_journal_lines AS l
JOIN gl_journal_entries AS e ON e.journal_entry_id = l.journal_entry_id
JOIN gl_accounts AS a        ON a.gl_account_id = l.gl_account_id
WHERE e.entry_date <= (SELECT as_of_date FROM dataset_info)
GROUP BY a.account_number, a.account_name, a.account_type
ORDER BY a.account_number;

What it returns. About twenty rows in account order. Assets and expenses net positive; liabilities, equity and revenue net negative. The receivables line equals the open total from query 1.

Audit tip

Total debits equal total credits, and the net column sums to zero. In cpafirm both hold to the cent.

Try it

Does the general ledger balance?

Across the whole general ledger, what are the total debits, the total credits and the difference? Round each figure to cents. Return one row.

Runs on cpafirm (SQL Server). Only read-only SELECT queries run here.

3. Revenue by month

Question. How much did we sell each month? Group by the first day of the month. Grouping by MONTH() alone merges January across years.

SELECT DATEFROMPARTS(YEAR(o.orderDate), MONTH(o.orderDate), 1) AS month,
       COUNT(DISTINCT o.orderNumber) AS orders,
       SUM(d.quantityOrdered * d.priceEach) AS sales
FROM classicmodels.orders AS o
JOIN classicmodels.orderdetails AS d ON d.orderNumber = o.orderNumber
GROUP BY DATEFROMPARTS(YEAR(o.orderDate), MONTH(o.orderDate), 1)
ORDER BY month;

What it returns. About 29 monthly rows. The labels move with the sample dates, but the shape of the series holds.

Control. The sales column sums to SUM(quantityOrdered * priceEach) over orderdetails with no grouping.

MariaDB. Use DATE_FORMAT(o.orderDate, '%Y-%m-01') in both the select list and the GROUP BY.

4. Year over year

Question. How did each month compare with the same month last year? Compare complete periods only. A partial year against a full prior year makes every month look like a decline.

WITH monthly AS (
    SELECT YEAR(issue_date) AS yr, MONTH(issue_date) AS mo, SUM(total_amount) AS billed
    FROM invoices
    WHERE YEAR(issue_date) IN (2024, 2025)
    GROUP BY YEAR(issue_date), MONTH(issue_date)
)
SELECT cy.mo, py.billed AS billed_2024, cy.billed AS billed_2025,
       ROUND((cy.billed - py.billed) * 100.0 / NULLIF(py.billed, 0), 1) AS yoy_pct
FROM monthly AS cy
JOIN monthly AS py ON py.mo = cy.mo AND py.yr = 2024
WHERE cy.yr = 2025
ORDER BY cy.mo;

What it returns. Twelve rows. In cpafirm 2025 is above 2024 in every month, by about 4 to 40 percent, unevenly.

Tip

NULLIF stops a zero prior month from dividing by zero.

Control. The two billed columns sum to the annual totals in invoices.

5. Duplicate detection

Question. Did we record the same payment twice? Test two things, and never delete on one test alone. First, same client, date and amount.

SELECT p.payment_id, p.client_id, p.payment_date, p.amount,
       p.payment_method, p.reference_number, p.received_by
FROM payments AS p
WHERE EXISTS (SELECT 1 FROM payments AS q
              WHERE q.client_id = p.client_id AND q.payment_date = p.payment_date
                AND q.amount = p.amount AND q.payment_id <> p.payment_id)
ORDER BY p.client_id, p.payment_date;

What it returns. Pairs, usually with different methods and reference numbers. In cpafirm there are a few dozen rows. They may be split payments, so treat them as a review list.

Second, a repeated reference number:

SELECT reference_number, COUNT(*) AS copies, COUNT(DISTINCT client_id) AS clients,
       MIN(amount) AS min_amt, MAX(amount) AS max_amt
FROM payments
GROUP BY reference_number
HAVING COUNT(*) > 1
ORDER BY copies DESC;

What it returns. In cpafirm every repeated reference belongs to different clients with different amounts. That is a problem with how references are generated, not a double payment. A true duplicate repeats the client and the amount, and this sample has none.

Try it

Payment references used three or more times

Which payment reference numbers are used by three or more payments? Show the reference, the number of payments and the number of different clients. Most used first, then by reference.

Runs on cpafirm (SQL Server). Only read-only SELECT queries run here.

6. Three-way match

Question. For each fixed-fee engagement, do the agreed fee, the time worked and the billing agree? Compare the order (the engagement fee), the delivery (recorded time) and the bill (invoice lines). A billing above a fixed fee is an exception.

WITH billed AS (
    SELECT engagement_id, SUM(amount) AS billed_amount FROM invoice_lines GROUP BY engagement_id
), matched AS (
    SELECT e.engagement_id, e.fee_amount AS agreed_fee,
           ISNULL(b.billed_amount, 0) AS billed_amount,
           CASE WHEN ISNULL(b.billed_amount, 0) = 0 THEN 'Work recorded, not billed'
                WHEN ISNULL(b.billed_amount, 0) > e.fee_amount THEN 'Billed above fixed fee'
                ELSE 'Billed within fixed fee' END AS match_status
    FROM engagements AS e
    LEFT JOIN billed AS b ON b.engagement_id = e.engagement_id
    WHERE e.fiscal_year = 2025 AND e.fee_type = 'Fixed' AND e.status = 'Complete'
)
SELECT match_status, COUNT(*) AS engagements,
       SUM(agreed_fee) AS agreed, SUM(billed_amount) AS billed
FROM matched
GROUP BY match_status
ORDER BY engagements DESC;

What it returns. Most completed fixed-fee engagements in fiscal 2025 were billed within the fee, and for that group the agreed and billed totals are equal. A smaller group was billed above the fee: those are the exceptions to review. The "not billed" row is empty for completed work.

Note

To add hours, sum time_entries per engagement in its own CTE. Do not join time to invoice lines directly, or rows multiply (section 13). MariaDB: IFNULL.

7. Gaps in sequence numbers

Question. Are numbered documents complete? LEAD looks at the next row in order, and a gap shows as a jump greater than one.

SELECT x.orderNumber, x.nxt, x.nxt - x.orderNumber - 1 AS missing
FROM (SELECT orderNumber, LEAD(orderNumber) OVER (ORDER BY orderNumber) AS nxt
      FROM classicmodels.orders) AS x
WHERE x.nxt - x.orderNumber > 1
ORDER BY x.orderNumber;

What it returns. No rows. The order numbers are complete. An empty result is a result, and it is the one you want.

Control: on gl_journal_entries in cpafirm the same test returns no rows, and the distinct count equals the maximum minus the minimum plus one.

8. Running balance

Question. What is a client's balance after each invoice and payment? Put invoices and payments in one list, with payments negative, and sum from the first row to the current row.

WITH lines AS (
    SELECT i.issue_date AS txn_date, i.invoice_number AS ref, 'Invoice' AS kind, i.total_amount AS amount
    FROM invoices AS i WHERE i.client_id = 812
    UNION ALL
    SELECT p.payment_date, p.reference_number, 'Payment', -p.amount
    FROM payments AS p WHERE p.client_id = 812
)
SELECT txn_date, kind, ref, amount,
       SUM(amount) OVER (ORDER BY txn_date, kind, ref
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance
FROM lines
ORDER BY txn_date, kind, ref;

What it returns. One row per invoice or payment. The balance rises with each invoice and falls with each payment, and this client returns to zero after most payments.

Control. The last running balance equals the sum of all amounts. Note. The kind and ref tie-breakers keep the order repeatable when two rows share a date.

9. Top customers and concentration

Question. How much revenue comes from the largest customers? SUM(...) OVER () with no partition is the grand total, so each share is measured against all sales.

WITH cust AS (
    SELECT o.customerNumber, SUM(d.quantityOrdered * d.priceEach) AS sales
    FROM classicmodels.orders AS o
    JOIN classicmodels.orderdetails AS d ON d.orderNumber = o.orderNumber
    GROUP BY o.customerNumber
), ranked AS (
    SELECT c.customerName, x.sales,
           ROW_NUMBER() OVER (ORDER BY x.sales DESC) AS rnk,
           x.sales * 100.0 / SUM(x.sales) OVER () AS share_pct,
           SUM(x.sales) OVER (ORDER BY x.sales DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 100.0
               / SUM(x.sales) OVER () AS cum_pct
    FROM cust AS x
    JOIN classicmodels.customers AS c ON c.customerNumber = x.customerNumber
)
SELECT rnk, customerName, ROUND(sales, 2) AS sales,
       ROUND(share_pct, 1) AS share_pct, ROUND(cum_pct, 1) AS cum_pct
FROM ranked
WHERE rnk <= 10
ORDER BY rnk;

What it returns. Ten rows. The largest customer is a little under a tenth of sales, the top two about 15 percent together, and the top ten about 28 percent. With about 98 customers, that is moderate concentration.

Control. The cumulative share on the last customer is 100. MariaDB: use LIMIT 10.

10. Reconciling two lists

Question. Does every payment received equal what we applied to invoices? Total each list by the shared key, join, and list each key that does not agree.

WITH applied AS (
    SELECT payment_id, SUM(amount_applied) AS applied_amount
    FROM payment_applications GROUP BY payment_id
)
SELECT p.payment_id, p.client_id, p.payment_date, p.amount AS received,
       ISNULL(a.applied_amount, 0) AS applied,
       p.amount - ISNULL(a.applied_amount, 0) AS unapplied
FROM payments AS p
LEFT JOIN applied AS a ON a.payment_id = p.payment_id
WHERE ABS(p.amount - ISNULL(a.applied_amount, 0)) > 0.005
ORDER BY unapplied DESC;

What it returns. Only payments that do not reconcile. In cpafirm there are none, so the result is empty, and that is the answer.

Control. Count mismatches and total both sides. In cpafirm the received and applied totals are equal to the cent across about 20,000 payments, and no application points to a missing payment.

11. Accruals by month with a numbers table

Question. How much of each grant should we recognize each month? A straight-line schedule spreads each award evenly over its term. A recursive CTE builds the list of months, so no calendar table is needed. Runs on nonprofit.

WITH months AS (
    SELECT CAST('2021-01-01' AS date) AS month_start
    UNION ALL
    SELECT DATEADD(month, 1, month_start) FROM months WHERE month_start < '2028-12-01'
), calc AS (
    SELECT g.grant_id, g.award_amount,
           DATEFROMPARTS(YEAR(g.start_date), MONTH(g.start_date), 1) AS first_m,
           DATEFROMPARTS(YEAR(g.end_date), MONTH(g.end_date), 1) AS last_m
    FROM grants AS g
), spread AS (
    SELECT m.month_start, c.grant_id,
           c.award_amount / (DATEDIFF(month, c.first_m, c.last_m) + 1) AS monthly_amount
    FROM months AS m
    JOIN calc AS c ON m.month_start BETWEEN c.first_m AND c.last_m
)
SELECT YEAR(month_start) AS yr, MONTH(month_start) AS mo,
       COUNT(*) AS grant_months, ROUND(SUM(monthly_amount), 2) AS recognized
FROM spread
WHERE YEAR(month_start) = 2025
GROUP BY YEAR(month_start), MONTH(month_start)
ORDER BY yr, mo
OPTION (MAXRECURSION 200);

What it returns. Twelve rows for 2025. Recognition is highest early in the year and falls through the middle, and the grant-month count shows how many awards were active.

Control. Run the same spread across all months and compare its total with SUM(award_amount) over grants, with no year filter. In nonprofit the two match across 94 grants. The month range must cover every grant: an earlier version stopped in 2027, and the totals came up short because some grants run into 2028. MariaDB: WITH RECURSIVE, DATE_ADD(d, INTERVAL 1 MONTH), TIMESTAMPDIFF(MONTH, a, b), DATE_SUB(d, INTERVAL DAYOFMONTH(d) - 1 DAY) in place of DATEFROMPARTS, and no OPTION clause.

12. Fund balances

Question. What is the result for each fund, and does each fund balance on its own? Every fund must balance, so its lines net to zero. The result is revenue minus expense. Both types are credit - debit, because expense accounts net negative. Runs on nonprofit.

SELECT f.fund_code, f.net_asset_class,
       SUM(CASE WHEN a.account_type IN ('Revenue', 'Expense')
                THEN l.credit - l.debit ELSE 0 END) AS result,
       SUM(l.debit - l.credit) AS all_lines_net
FROM gl_journal_lines AS l
JOIN gl_accounts AS a ON a.gl_account_id = l.gl_account_id
JOIN funds AS f       ON f.fund_id = l.fund_id
GROUP BY f.fund_code, f.net_asset_class
ORDER BY f.fund_code;

What it returns. One row per fund, 102 in the sample, with all_lines_net zero for every one. A negative result is a year-to-date deficit.

Control. For the General Operating fund, vw_fund_balances shows opening net assets of 2,030,000 and ending net assets of about 763,000. This query's result is about negative 1.27 million, and opening plus result equals ending. MariaDB: the query runs unchanged.

13. How not to double count after joins

A join multiplies rows. One invoice with three lines and two applications becomes six rows, so SUM(total_amount) counts it six times. The naive version joins everything and sums the invoice total. The second row sums the same client's invoices with no joins:

SELECT 'naive join' AS method, SUM(i.total_amount) AS billed, COUNT(*) AS rows_in_join
FROM invoices AS i
JOIN invoice_lines AS l ON l.invoice_id = i.invoice_id
JOIN payment_applications AS pa ON pa.invoice_id = i.invoice_id
WHERE i.client_id = 812
UNION ALL
SELECT 'aggregate first', SUM(i.total_amount), COUNT(*)
FROM invoices AS i WHERE i.client_id = 812;

What it returns. For client 812 the naive join sums to about 13,490 over 29 rows. The correct total over 25 invoices is about 8,140. The naive figure is overstated by the fan-out.

The fix aggregates each side to one row per invoice, then ties the result back:

WITH lines AS (
    SELECT invoice_id, SUM(amount) AS line_total FROM invoice_lines GROUP BY invoice_id
), paid AS (
    SELECT invoice_id, SUM(amount_applied) AS paid_total FROM payment_applications GROUP BY invoice_id
)
SELECT SUM(i.total_amount) AS billed,
       SUM(ISNULL(l.line_total, 0)) AS lines_tie,
       SUM(ISNULL(p.paid_total, 0)) AS paid
FROM invoices AS i
LEFT JOIN lines AS l ON l.invoice_id = i.invoice_id
LEFT JOIN paid AS p  ON p.invoice_id = i.invoice_id
WHERE i.client_id = 812;

What it returns. billed and lines_tie are both about 8,140, and paid is about 7,890.

Control. lines_tie must equal billed. Across the whole cpafirm sample the line total equals the invoice total exactly. Rule for joins: write down the grain of each table, aggregate one side when both are "many" on the key, and count rows before and after each join.

Where next

SQL deep end covers the window functions used here. CPA firm practice management and Nonprofit donors, grants and funds describe the models behind these queries. DAX for financial reporting and Power Query for accounting data do the same work in Power BI.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.