SQL for Financial Reporting: Trial Balance, Period Close, and Reconciliation Queries

SQL for financial reporting differs from general analytics in one way that matters: every number must tie to something. A dashboard that is 2 percent off is a curiosity. A trial balance that is off by one cent is a failed control. This article covers the query patterns finance and IT teams use for trial balances, period filters, running totals, variance, aging, FX, and reconciliation.

I have supported finance reporting in hospital systems and multi-entity organizations, usually as the analyst asked why two reports disagreed at month end. The queries below come from that work. The schema is generic, so adapt names to your ERP. This is not accounting or audit advice, and your controller owns the definitions.

How SQL for Financial Reporting Differs from Analytics SQL

Analytics tolerates approximation. Finance does not. Four properties separate the two, and each changes how you write the query.

The last row decides disputes. When two reports disagree, the policy owner picks the right one. Your job is to show exactly where they differ and why, using queries both teams can read.

A Reference Schema for Financial Reporting Queries

ERP systems differ, but most general ledgers reduce to the same five structures. The examples below use these names so you can map them to Oracle, SAP, Dynamics, NetSuite, or a warehouse.

Two columns deserve attention before you write a query. posting_date is when the entry hits the ledger, and it may differ from the transaction date. created_at is when the row was written, and it lets you rebuild what a report looked like on a past day. Both come up in the scenarios below.

Sub-ledgers
AR, AP, payroll, billing

Manual journals
Accruals, adjustments

FX and budget
Reference data

General ledger
gl_entries
Debits = credits

Trial balance
By account, period

Variance
Actual vs budget

Statements
P&L, balance

Tie-out checks run at each arrow. Sub-ledgers must equal their control accounts in the general ledger. The ledger must balance. Statements must equal the trial balance. When a report fails to tie, start at the first arrow where the totals diverge.

Eleven Query Patterns for Financial Reporting

Pattern 1: Prove the ledger balances

Every journal must have debits equal to credits. Test this before any report, because an unbalanced journal poisons every figure downstream.

SELECT  journal_id,
        SUM(debit)  AS total_debit,
        SUM(credit) AS total_credit,
        SUM(debit) - SUM(credit) AS out_of_balance
FROM    gl_entries
WHERE   period_id = 202609
GROUP BY journal_id
HAVING  SUM(debit) <> SUM(credit)
ORDER BY ABS(SUM(debit) - SUM(credit)) DESC;

A clean ledger returns zero rows. Count the journals you checked as well, as in the validation work covered in the validation query families article. A zero from an empty period is not a pass.

Pattern 2: Trial balance by account and period

SELECT  a.account_id, a.account_type,
        SUM(g.debit)  AS debits,
        SUM(g.credit) AS credits,
        CASE WHEN a.normal_balance = 'D' THEN SUM(g.debit) - SUM(g.credit)
             ELSE SUM(g.credit) - SUM(g.debit) END AS ending_balance
FROM    gl_entries g
JOIN    accounts a ON a.account_id = g.account_id
WHERE   g.period_id = 202609
GROUP BY a.account_id, a.account_type, a.normal_balance
ORDER BY a.account_id;

The normal_balance column makes assets and expenses show positive when they carry debits, and liabilities and revenue show positive when they carry credits. Without it, half your accounts display with the wrong sign and a reviewer spends an hour confirming they are only reversed.

Pattern 3: Filter by fiscal period, not by calendar month

Calendar logic fails the first time a fiscal year starts in July or a retailer uses a 4-4-5 calendar. Join to a fiscal calendar table instead of using MONTH().

SELECT  c.fiscal_year, c.period_no, SUM(g.credit - g.debit) AS revenue
FROM    gl_entries g
JOIN    accounts a        ON a.account_id = g.account_id AND a.account_type = 'REVENUE'
JOIN    fiscal_calendar c ON c.period_id  = g.period_id
WHERE   c.fiscal_year = 2026
GROUP BY c.fiscal_year, c.period_no
ORDER BY c.period_no;

If your warehouse has no calendar table, build one. It is a small table that pays for itself in every report. Include the open or closed flag, since reports on closed periods should return stable results.

Pattern 4: Year-to-date and running totals

Window functions compute cumulative totals without self-joins. The ORDER BY and frame clause control the running sum, and the Microsoft OVER clause documentation lists the frame options.

WITH monthly AS (
    SELECT c.fiscal_year, c.period_no, SUM(g.credit - g.debit) AS revenue
    FROM   gl_entries g
    JOIN   accounts a        ON a.account_id = g.account_id AND a.account_type = 'REVENUE'
    JOIN   fiscal_calendar c ON c.period_id  = g.period_id
    GROUP BY c.fiscal_year, c.period_no)
SELECT  fiscal_year, period_no, revenue,
        SUM(revenue) OVER (PARTITION BY fiscal_year ORDER BY period_no
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS revenue_ytd
FROM    monthly
ORDER BY fiscal_year, period_no;

Specify ROWS explicitly. The default frame is RANGE, which includes every row with an equal sort value at once. That changes the result when sort values repeat.

Pattern 5: Actual versus budget variance

SELECT  b.account_id, b.cost_center_id,
        b.amount                              AS budget,
        ISNULL(x.actual, 0)                   AS actual,
        ISNULL(x.actual, 0) - b.amount        AS variance,
        CASE WHEN b.amount = 0 THEN NULL
             ELSE CAST(100.0 * (ISNULL(x.actual, 0) - b.amount) / b.amount AS decimal(9,2)) END AS variance_pct
FROM    budget b
LEFT JOIN (SELECT account_id, cost_center_id, SUM(debit - credit) AS actual
           FROM gl_entries WHERE period_id = 202609
           GROUP BY account_id, cost_center_id) x
       ON x.account_id = b.account_id AND x.cost_center_id = b.cost_center_id
WHERE   b.period_id = 202609;

Start from the budget and LEFT JOIN actuals, so lines with a budget and no spending still appear. A FULL OUTER JOIN also catches spending with no budget line, which usually signals a coding error. Guard the percentage against a zero budget, and decide whether a null or a flag should show instead.

Pattern 6: Aging buckets for receivables

SELECT  customer_id,
        SUM(CASE WHEN DATEDIFF(day, due_date, @as_of) <= 0               THEN balance ELSE 0 END) AS current_bucket,
        SUM(CASE WHEN DATEDIFF(day, due_date, @as_of) BETWEEN 1  AND 30  THEN balance ELSE 0 END) AS d1_30,
        SUM(CASE WHEN DATEDIFF(day, due_date, @as_of) BETWEEN 31 AND 60  THEN balance ELSE 0 END) AS d31_60,
        SUM(CASE WHEN DATEDIFF(day, due_date, @as_of) BETWEEN 61 AND 90  THEN balance ELSE 0 END) AS d61_90,
        SUM(CASE WHEN DATEDIFF(day, due_date, @as_of) > 90               THEN balance ELSE 0 END) AS d90_plus,
        SUM(balance) AS total
FROM    ar_open_items
WHERE   balance <> 0
GROUP BY customer_id;

Pass the as-of date as a parameter. Never use GETDATE() inside an aging report that finance will compare to a closed period. The bucket totals must equal the AR control account, so add that reconciliation as a separate query.

Pattern 7: Reconcile a sub-ledger to the general ledger

SELECT  sub.total_open  AS subledger_total,
        gl.balance      AS gl_control_balance,
        sub.total_open - gl.balance AS difference
FROM   (SELECT SUM(balance) AS total_open FROM ar_open_items) sub
CROSS JOIN
       (SELECT SUM(debit - credit) AS balance
        FROM gl_entries WHERE account_id = 120000 AND period_id <= 202609) gl;   -- AR control account

A non-zero difference does not always mean an error. Timing differences, such as an invoice posted to the sub-ledger on the 30th and to the ledger on the 1st, create explainable gaps. List them as reconciling items with an owner, never plug them silently.

Pattern 8: Convert currencies with the right rate date

SELECT  g.entry_id, g.currency, g.debit - g.credit AS local_amount,
        r.rate,
        CAST((g.debit - g.credit) * r.rate AS decimal(19,4)) AS usd_amount
FROM    gl_entries g
OUTER APPLY (SELECT TOP 1 f.rate
             FROM fx_rates f
             WHERE f.from_ccy = g.currency AND f.to_ccy = 'USD'
               AND f.rate_date <= g.posting_date          -- rate in force on the posting date
             ORDER BY f.rate_date DESC) r
WHERE   g.period_id = 202609 AND g.currency <> 'USD';

Accounting policy decides which rate applies. Income statement items commonly use an average or transaction-date rate. Balance sheet items commonly use the period-end rate. Confirm the policy with your controller, and store the rate type in the query name so the next analyst sees it.

Pattern 9: Exclude reversed entries correctly

Accountants correct errors by posting a reversing entry, not by deleting the original. A report that sums everything includes both and nets to zero, which is right for a balance. A report that counts entries or lists transactions must exclude the pair.

SELECT  g.entry_id, g.account_id, g.debit, g.credit
FROM    gl_entries g
WHERE   g.period_id = 202609
  AND   g.reversal_of IS NULL                                  -- not itself a reversal
  AND   NOT EXISTS (SELECT 1 FROM gl_entries r
                    WHERE r.reversal_of = g.entry_id);          -- not reversed by another entry

Pattern 10: Rebuild a report as of a past date

A controller asks why last Tuesday’s report showed a different total than today’s. Entries posted late to an open period change the answer. Use created_at to recreate the earlier view.

SELECT  account_id, SUM(debit - credit) AS balance_as_of_then
FROM    gl_entries
WHERE   period_id = 202609
  AND   created_at <= '2026-10-06 17:00:00'        -- the report run time
GROUP BY account_id;

This works only if your ERP never updates rows in place. Many systems do update status columns, so ask your DBA whether history is preserved. Where it is not, take a snapshot of the trial balance at each report run and store it.

Pattern 11: Round once, at the end

Rounding at line level and summing the result produces a different total from summing first and rounding once. Neither is wrong in isolation. Finance policy chooses, and the query must match the ledger.

-- Money columns: fixed precision, never float
CREATE TABLE gl_entries_example (
    entry_id BIGINT PRIMARY KEY,
    debit    DECIMAL(19,4) NOT NULL DEFAULT 0,
    credit   DECIMAL(19,4) NOT NULL DEFAULT 0
);

Use DECIMAL(19,4) or the precision your ERP uses. Floating point types store binary approximations, so a column of 0.10 values can sum to 0.30000000000000004. That one cent breaks a tie-out.

Scenario: Net Patient Revenue That Missed the Ledger by $412,000

A 300-bed hospital produced a monthly net patient revenue report from the data warehouse. Finance compared it to the general ledger on close day three. The report showed $412,000 less revenue than the ledger for September.

I was the analyst asked to reconcile before the controller signed the board package. The warehouse job ran at 6 a.m. on close day one. Finance posted accrual and contractual adjustment journals on days two and three.

The query that settled it combined Patterns 1 and 10. I grouped the difference by journal source and created time, which showed two manual journals posted on close day two.

SELECT  j.source_system, j.journal_id, MIN(g.created_at) AS first_posted,
        SUM(g.credit - g.debit) AS revenue_effect
FROM    gl_entries g
JOIN    journals j ON j.journal_id = g.journal_id
JOIN    accounts a ON a.account_id = g.account_id AND a.account_type = 'REVENUE'
WHERE   g.period_id = 202609
  AND   g.created_at > '2026-10-01 06:00:00'     -- after the warehouse load
GROUP BY j.source_system, j.journal_id
ORDER BY ABS(SUM(g.credit - g.debit)) DESC;

Both journals released excess reserves for contractual allowances, which raised net revenue. The warehouse had loaded before they posted, so it carried the old, lower figure. I attach the journal descriptions to every reconciliation, because a number without its narrative invites the wrong conclusion.

The fix was a scheduling change, not a code change. The team moved the revenue load behind the “manual journals posted” checkpoint and added a late-posting variance check to the close checklist. For hospital data feeds, such as the account tables behind charges, the Epic Clarity SQL guide shows where billing data originates.

Cost Center Rollups with a Recursive CTE

Finance reports roll cost centers up a hierarchy: department, division, entity. Flat joins cannot do this when the depth varies. A recursive common table expression walks the tree once and gives every node its ancestors.

WITH tree AS (
    SELECT cost_center_id, parent_id, cost_center_id AS root_id, 0 AS depth
    FROM   cost_centers
    WHERE  parent_id IS NULL
    UNION ALL
    SELECT c.cost_center_id, c.parent_id, t.root_id, t.depth + 1
    FROM   cost_centers c
    JOIN   tree t ON c.parent_id = t.cost_center_id
)
SELECT  t.root_id AS division, SUM(g.debit - g.credit) AS expense
FROM    gl_entries g
JOIN    tree t ON t.cost_center_id = g.cost_center_id
JOIN    accounts a ON a.account_id = g.account_id AND a.account_type = 'EXPENSE'
WHERE   g.period_id = 202609
GROUP BY t.root_id
OPTION (MAXRECURSION 20);

Set a recursion limit. A cycle in the hierarchy, such as a department that lists itself as its own parent, otherwise loops until the server stops it. Add a test that counts cost centers whose root is missing, because an orphaned node drops its spending out of every rollup.

Hierarchies change. A department that moves divisions mid-year raises a policy question: report history under the old structure or the new one? Store effective dates on the hierarchy and decide with finance before the first report ships.

Intercompany Eliminations and the Zero-Sum Test

When two entities in a group trade with each other, one books a receivable and the other a payable. Consolidated statements must remove both. A simple test confirms the eliminations work.

-- Intercompany accounts must net to zero across entities after eliminations
SELECT  SUM(g.debit - g.credit) AS net_intercompany
FROM    gl_entries g
JOIN    accounts a ON a.account_id = g.account_id AND a.is_intercompany = 1
WHERE   g.period_id = 202609;

A non-zero result means a one-sided entry or a rate difference between the two entities’ books. Group by counterparty entity to find the pair, then compare each side’s amount and currency.

Making Large Ledger Queries Practical

General ledger tables run to hundreds of millions of rows at large organizations. A trial balance that scans all of them every morning wastes time and competes with close-week jobs. Three techniques help.

-- Store a trial balance snapshot when a period closes
INSERT INTO rpt.trial_balance_snapshot
       (period_id, account_id, debits, credits, source_row_count, snapped_at)
SELECT  g.period_id, g.account_id, SUM(g.debit), SUM(g.credit), COUNT(*), SYSDATETIME()
FROM    gl_entries g
WHERE   g.period_id = 202609
GROUP BY g.period_id, g.account_id;

Compare any later report for that period to the snapshot. A difference tells you someone posted to a closed period or changed history, and it hands you the evidence for the conversation.

Mapping Hospital Revenue Cycle Data to the Ledger

On healthcare projects, the general ledger receives summaries from patient accounting, not individual charges. Knowing the mapping helps you trace a ledger variance back to its clinical source. The table shows the typical path.

The September variance above fits this pattern. Manual journals for contractual reserves sat outside the charge and payment feeds. A warehouse built only from those feeds could never match the ledger. When you meet a healthcare ledger gap, ask first whether manual journals explain it.

Scenario: A Multi-Currency Report That Restated Last Year’s Revenue Every Day

A software company with subsidiaries in four countries reported consolidated revenue in US dollars. The FP&A team noticed that prior-quarter revenue changed slightly every morning. No journals had posted to the closed quarter. The controller asked IT to find out why.

The report converted local-currency amounts with the latest available rate. Every morning the rate table loaded a new row, and the query picked the newest one for all periods. History moved with the market.

The before query

-- BEFORE: newest rate applied to every period
SELECT  g.period_id, SUM((g.credit - g.debit) * r.rate) AS revenue_usd
FROM    gl_entries g
JOIN    fx_rates r ON r.from_ccy = g.currency AND r.to_ccy = 'USD'
                  AND r.rate_date = (SELECT MAX(rate_date) FROM fx_rates)
WHERE   g.account_id BETWEEN 400000 AND 499999
GROUP BY g.period_id;

The after query

-- AFTER: average rate for each fiscal period, per policy
WITH period_rate AS (
    SELECT c.period_id, f.from_ccy, AVG(f.rate) AS avg_rate
    FROM   fiscal_calendar c
    JOIN   fx_rates f ON f.to_ccy = 'USD' AND f.rate_date BETWEEN c.start_date AND c.end_date
    GROUP BY c.period_id, f.from_ccy)
SELECT  g.period_id,
        CAST(SUM((g.credit - g.debit) * ISNULL(pr.avg_rate, 1)) AS decimal(19,2)) AS revenue_usd
FROM    gl_entries g
LEFT JOIN period_rate pr ON pr.period_id = g.period_id AND pr.from_ccy = g.currency
WHERE   g.account_id BETWEEN 400000 AND 499999
GROUP BY g.period_id;

The after query is stable for closed periods because each period uses only its own dates. One flaw remains in that code, and it matters. ISNULL(pr.avg_rate, 1) silently treats a missing rate as parity, which would pass an unconverted currency as dollars. In production I raise an error or flag the row instead of defaulting to 1.

Finance policy sets the rate type for each statement line. I ask for the policy in writing and put its name in a comment at the top of the query. Then I test one subsidiary by hand against the treasury rate file.

Decision Tree: Why Doesn’t This Report Tie Out?

When a report and the ledger disagree, resist guessing. Walk the tree from the top and stop at the first check that fails.

Report total differs from the ledger
Write down the exact difference

1. Same period, same entity, same accounts?
Fix filters and mappings first

2. Do debits equal credits in both sources?
Pattern 1 on each side

3. Does the gap follow posting time?
Pattern 10: compare created_at to the load time

4. Does the gap follow currency or rounding?
Patterns 8 and 11: rate date, rate type, precision

5. Reversals, eliminations, manual adjustments

Most gaps resolve at step one or three. Wrong filters and timing account for the majority I have seen. FX and rounding explain a smaller share, and reversals explain the stubborn remainder.

Controls and Audit Trails for Financial Queries

Public companies assess internal control over financial reporting, and auditors test it. PCAOB AS 2201 describes how auditors approach that assessment for integrated audits. IT general controls feed into it, including access, change management, and operations. Reports that support financial statements fall inside that scope.

You do not need to be an auditor to write defensible queries. Make them reproducible, reviewed, and traceable. The table below lists what an auditor typically asks for and how to prepare.

Separate duties where you can. The person who writes a revenue query should not be the only one who approves it. A second reviewer catches definition errors that the author cannot see.

This section describes common practice, not an audit opinion. Your internal audit and compliance teams define the controls that apply to your organization.

Template: Financial Report Specification for Business Analysts

Use this sheet with your finance partner before development starts. A completed copy shortens the review cycle and gives auditors the definition they will ask for.

Each business analyst should see this sheet before a single line of SQL exists. The tie-out and late-posting rows prevent the two failures that cost the most time in close week.

Close-week reconciliation sign-off

Role Checklists for Financial Reporting Projects

Common Mistakes in Financial Reporting SQL

When I Would Use SQL, Excel, a BI Tool, or the ERP’s Own Reports

I use SQL when the number must tie and the work must be repeatable. I avoid it for statutory statements that the ERP vendor already supports, since local reimplementation creates a second source of truth. For practice with ledger-style problems on safe data, try the SQL Analyst Challenge Room, and see the SQL for IT Analysts hub for related guides.

Edge Cases That Break Clean Financial Logic

Questions Analysts Ask About Financial Reporting SQL

Which data type should I use for currency amounts?

Use fixed-precision decimals such as DECIMAL(19,4). Floating point types store approximations and break tie-outs.

Should I query the ERP database directly?

Only with approval and read access to a reporting copy. Production ERP tables carry locking risk, and vendors often discourage direct access. A replicated reporting schema is safer.

How do I report on an open period?

Label the report as preliminary and show the run time. Open periods change as entries post, so a stable number requires a closed period or a stored snapshot.

Why does my total differ by a few cents?

Check rounding order, decimal precision, and currency conversion. Compare the sum of rounded lines to the rounded sum.

Run This Before Your Next Close

Pick one report finance relies on. Write the tie-out target in one line, then run Pattern 1 and Pattern 2 for the last closed period. If the report does not equal the trial balance to the penny, find the first arrow in the data flow where the totals diverge. That hour of work saves the controller a long night.

Sources and Further Reading

• PCAOB AS 2201: An Audit of Internal Control Over Financial Reporting. Auditing standard behind the control questions above.
• Microsoft Learn: SELECT – OVER clause (Transact-SQL). Window functions and frame options.
• Your ERP vendor’s data dictionary and your organization’s accounting policy manual. Authority for table meanings and rate policies.

Schema names are generic and need mapping to your ERP. Accounting treatments depend on your framework and policies, so confirm each with your controller. This article is not accounting, tax, or audit advice.