SQL for Month-End Close: Automating Reconciliations

Reconciliation is fundamentally a matching problem: two datasets that should agree — the bank statement and the ledger, the sub-ledger and the general ledger, this month's balances and last month's plus movements — and a manual check for where they don't. That's exactly the kind of problem SQL is built for, and a handful of query patterns cover most of the manual VLOOKUP-and-eyeball work that reconciliation still involves at a lot of finance teams.

The core pattern: LEFT JOIN to find what's missing

The single most useful reconciliation query is a LEFT JOIN that reveals rows with no match on the other side. SELECT b.* FROM bank_transactions b LEFT JOIN ledger_entries l ON b.reference = l.reference WHERE l.reference IS NULL; returns every bank transaction that has no corresponding ledger entry — exactly the exception list a manual reconciliation is trying to produce by hand. Run it the other way round (ledger LEFT JOIN bank) to find ledger entries with no matching bank transaction, and you've covered both directions of the mismatch in two queries instead of a side-by-side manual scan.

Finding duplicates

Duplicate entries are a common reconciliation issue that's easy to catch with GROUP BY and HAVING: SELECT reference, COUNT(*) FROM ledger_entries GROUP BY reference HAVING COUNT(*) > 1; surfaces any reference number that appears more than once, which usually means a transaction was posted twice or a reference wasn't unique when it should have been.

Matches that don't quite match: rounding and timing

A lot of "unmatched" items aren't really unmatched — they're the same transaction with a rounding difference or a timing lag between when it hit the bank and when it was posted to the ledger. A tolerance-based join catches these instead of flagging every rounding cent as an exception: SELECT b.reference, b.amount, l.amount FROM bank_transactions b JOIN ledger_entries l ON b.reference = l.reference WHERE ABS(b.amount - l.amount) > 0.01; finds genuine amount mismatches while ignoring exact matches, and a small tolerance threshold (a cent, or whatever's appropriate) filters out rounding noise from real discrepancies worth investigating.

A worked example: bank vs ledger reconciliation

Put together, a basic month-end bank reconciliation might run three queries: unmatched bank transactions (bank LEFT JOIN ledger, ledger side null), unmatched ledger entries (the reverse), and amount mismatches on the references that do match but disagree. Between the three, you get a complete exception report — everything that needs a human look — while every item that matches cleanly on both reference and amount never has to be reviewed at all. That's the actual time saving: reconciliation effort scales with the number of exceptions, not the number of transactions, once SQL is doing the matching.

Turning this into something repeatable

A one-off query solves this month's reconciliation. Saving it as a view — CREATE VIEW unmatched_bank_transactions AS SELECT ... — means next month's reconciliation is SELECT * FROM unmatched_bank_transactions instead of rewriting the join from scratch. Where the database supports it, a scheduled job that runs the reconciliation queries automatically and lands the exception list somewhere accessible (a table, a report, an email) turns this from "a query I run manually each month" into an actual automated first pass — someone still reviews the exceptions, but nobody's building the exception list by hand.

What this replaces, and what it doesn't

SQL-based reconciliation replaces the mechanical matching — the part where a human scans two lists looking for what doesn't line up. It doesn't replace judgment: deciding whether a genuine mismatch is a data entry error, a timing difference that'll resolve itself next period, or something that needs escalating still needs a person. The value is in shrinking what a person has to look at from every transaction to only the exceptions, not in removing the reviewer from the process entirely.

Getting started without disrupting an existing process

Don't try to automate the whole month-end close in one go. Pick the single most time-consuming reconciliation — usually the one with the most line items or the most manual cross-checking — and build the LEFT JOIN pattern for just that one first. Once it's producing a reliable exception list that matches what the manual process used to catch, it's earned the trust to replace that manual step, and the same pattern extends naturally to the next reconciliation on the list.

Where this builds from

Everything here rests on SQL joins — reconciliation is really just LEFT JOIN applied to a specific, recurring finance problem. And if you need period-over-period variance alongside the reconciliation itself, SQL window functions handle that comparison in the same query rather than as a separate manual step.

A note on access and data quality before you start

Two things worth checking before building any reconciliation query. First, access: reconciliation queries should run against a read-only reporting copy of the data, not live production tables, for the same reasons that apply to any recurring finance query against a transactional system. Second, reference consistency: LEFT JOIN matching only works cleanly if both sides use the same reference format — a bank feed that stores "INV-1023" and a ledger that stores "1023" won't match on a straight equality join, and that mismatch needs fixing (often with a string transformation in the query itself) before the reconciliation logic above will produce a trustworthy exception list.

For the groundwork and the Excel alternative, see SQL for finance professionals and automating the same steps with Excel macros. Our Tech and Tools CPD for accountants turns the same material into CPD-recognised hours. Also worth reading: data analytics for accountants.