Excel is where finance analysis happens, but it is rarely where finance data lives. ERP systems like SAP, Oracle, NetSuite, and Microsoft Dynamics store transactions across dozens of normalized tables designed for transaction processing, not reporting. SQL is the tool that reaches into those tables and pulls out exactly the numbers you need, shaped the way you need them, before they ever reach a spreadsheet or dashboard.
This guide covers how general ledger data is typically structured, the SQL patterns finance teams use most often, and the mistakes that quietly produce wrong numbers.
On this page
- Why finance teams need SQL, not just Excel
- How general ledger data is structured
- Step 1: Pull raw GL transactions for a period
- Step 2: Join to the chart of accounts
- Step 3: Build a P&L summary
- Step 4: Build a balance sheet snapshot
- Step 5: Variance analysis, actual vs. budget
- Step 6: Month-over-month and YTD with window functions
- Common ERP table reference
- Common mistakes
- FAQ
Why Finance Teams Need SQL, Not Just Excel
A mid-size company's general ledger can easily contain millions of line items once you include every journal entry across every subsidiary and year. Pulling that into Excel directly is slow, error-prone, and usually capped by row limits. SQL lets you filter, join, and aggregate at the database level, so Excel or Power BI only ever receives the clean, summarized result you actually need.
Read access to your finance data source (a reporting database, data warehouse, or a replicated copy of your ERP data), a basic understanding of SELECT, WHERE, GROUP BY, and JOIN, and a list of the specific account codes or cost centers relevant to your reports.
How General Ledger Data Is Structured
Almost every ERP, regardless of vendor, organizes financial data the same basic way: a header table for each transaction, a line-item table with the actual debit and credit amounts, and reference tables for accounts, departments, vendors, and customers.
The reference table below maps this generic structure onto four widely used systems, so you know roughly what to search for once you are inside an unfamiliar schema.
Step 1: Pull Raw GL Transactions for a Period
Start narrow, then widen
Always filter by date range first. GL tables are typically the largest tables in the entire database, and an unfiltered query can lock up a shared reporting server.
SELECT gl.transaction_date, gl.account_code, gl.debit_amount, gl.credit_amount, gl.document_id FROM gl_transaction_lines gl WHERE gl.transaction_date BETWEEN '2026-01-01' AND '2026-01-31' AND gl.company_code = '1000';
Step 2: Join to the Chart of Accounts
Turn account codes into readable names
Raw GL data only has account codes, not labels. Join the chart of accounts table to get account names and categories, which is what makes the output usable for anyone outside the finance system team.
SELECT gl.transaction_date, coa.account_name, coa.account_category, gl.debit_amount, gl.credit_amount FROM gl_transaction_lines gl JOIN chart_of_accounts coa ON gl.account_code = coa.account_code WHERE gl.transaction_date BETWEEN '2026-01-01' AND '2026-01-31' AND gl.company_code = '1000';
Step 3: Build a P&L Summary
Aggregate by account category
A profit and loss view is really just GL activity grouped by account category, with revenue and expense accounts netted appropriately. This example assumes revenue accounts are stored as credits and expense accounts as debits, which is the common convention.
SELECT
coa.account_category,
SUM(gl.credit_amount) - SUM(gl.debit_amount) AS net_amount
FROM gl_transaction_lines gl
JOIN chart_of_accounts coa
ON gl.account_code = coa.account_code
WHERE gl.transaction_date BETWEEN '2026-01-01' AND '2026-01-31'
AND coa.account_category IN ('Revenue', 'COGS', 'Operating Expense')
GROUP BY coa.account_category
ORDER BY coa.account_category;
Revenue accounts will show a positive net amount under this convention; expense and COGS accounts will show a negative one. Flip the sign in your presentation layer, not in the underlying query, so the raw numbers stay auditable.
Step 4: Build a Balance Sheet Snapshot
Cumulative balance as of a date
Unlike a P&L, which resets each period, a balance sheet is cumulative from the beginning of the ledger. The query below sums every transaction up to and including the snapshot date, not just within one period.
SELECT
coa.account_name,
coa.account_category,
SUM(gl.debit_amount) - SUM(gl.credit_amount) AS ending_balance
FROM gl_transaction_lines gl
JOIN chart_of_accounts coa
ON gl.account_code = coa.account_code
WHERE gl.transaction_date <= '2026-01-31'
AND coa.account_category IN ('Asset', 'Liability', 'Equity')
GROUP BY coa.account_name, coa.account_category
ORDER BY coa.account_category, coa.account_name;
Step 5: Variance Analysis, Actual vs. Budget
Join actuals to a budget table
Variance analysis is almost always a join between your aggregated actuals query and a separate budget or forecast table, keyed on account and period.
WITH actuals AS (
SELECT
coa.account_category,
SUM(gl.credit_amount) - SUM(gl.debit_amount) AS actual_amount
FROM gl_transaction_lines gl
JOIN chart_of_accounts coa ON gl.account_code = coa.account_code
WHERE gl.transaction_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY coa.account_category
)
SELECT
b.account_category,
b.budget_amount,
a.actual_amount,
a.actual_amount - b.budget_amount AS variance
FROM budget b
JOIN actuals a ON b.account_category = a.account_category
WHERE b.period = '2026-01';
Using a common table expression (CTE) for the actuals keeps the aggregation logic separate and readable, rather than burying it inside a larger join.
Step 6: Month-over-Month and YTD with Window Functions
Compare periods without a self-join
Window functions let you reference a prior period's value in the same row, which avoids the row-multiplying problems that come from self-joining a large GL table.
SELECT
period_month,
monthly_total,
LAG(monthly_total) OVER (ORDER BY period_month) AS prior_month_total,
monthly_total - LAG(monthly_total) OVER (ORDER BY period_month) AS month_over_month_change,
SUM(monthly_total) OVER (ORDER BY period_month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS year_to_date_total
FROM monthly_revenue_summary
WHERE period_month BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY period_month;
This assumes a pre-aggregated monthly_revenue_summary table or view. In practice, this is usually built as a view on top of the Step 3 query, rather than repeating the full GL join every time.
Common ERP Table Reference
| System | Header table | Line-item table | Chart of accounts | Notes |
|---|---|---|---|---|
| SAP ECC / S/4HANA | BKPF | BSEG (or ACDOCA in S/4HANA) | SKA1 / SKAT | S/4HANA consolidates most reporting onto the ACDOCA table; BSEG is retained mainly for compatibility |
| Oracle E-Business Suite / Fusion | GL_JE_HEADERS | GL_JE_LINES | GL_CODE_COMBINATIONS | Account segments are often combined into a single code combination ID |
| NetSuite | Transaction | TransactionLine | Account | Queried through SuiteQL rather than a direct database connection |
| Microsoft Dynamics 365 F&O | LedgerJournalTrans | GeneralJournalAccountEntry | MainAccount | Newer versions route most reporting through GeneralJournalAccountEntry |
| QuickBooks Online | Not directly exposed | Not directly exposed | Not directly exposed | Data is pulled through the QuickBooks reporting API or a third-party connector, not a direct SQL connection |
Row-multiplying joins. Joining a header table to two different one-to-many child tables in the same query (for example, line items and tax details) multiplies rows and inflates totals. Aggregate each child table separately before joining, or join them one at a time and check row counts as you go.
Getting debit and credit signs backwards. Whether an account increases with a debit or a credit depends on its account type. Assets and expenses normally increase with debits; liabilities, equity, and revenue normally increase with credits. Reversing this silently flips your entire report.
Forgetting to exclude reversed or voided entries. Many ERPs keep reversed journal entries in the same table as the original, flagged by a status or document type field. Missing that filter double-counts activity that was later reversed.
Mixing fiscal year and calendar year. A company with a fiscal year starting in April will have period numbers that do not match calendar months. Always confirm which calendar the period fields actually follow before filtering by date.
Querying the live transactional database directly. Large aggregation queries against a production ERP database can slow down the system for everyone entering transactions. Use a reporting replica or data warehouse whenever one is available.
Key Takeaways
- Almost every ERP separates transactions into a header table and a line-item table, with a separate chart of accounts for labels.
- Always filter by date range first; GL tables are typically the largest in the database.
- A P&L is GL activity grouped by account category within a period; a balance sheet is a cumulative balance as of a date.
- Use a CTE to keep actuals aggregation separate from the budget join in variance analysis.
- Window functions handle month-over-month and year-to-date comparisons without the row-multiplying risk of a self-join.
- Table names differ by ERP, but the underlying header, line-item, and chart-of-accounts pattern is close to universal.
Frequently Asked Questions
Do finance teams really need SQL if they already use Excel?
Excel works well once data is already extracted, but most ERP and accounting systems store millions of transaction rows across normalized tables. SQL is what actually retrieves and shapes that data before it ever reaches Excel or Power BI.
What is the difference between the general ledger and sub-ledgers?
The general ledger holds summarized financial transactions by account. Sub-ledgers, such as accounts payable or accounts receivable, hold the detailed supporting transactions that roll up into the general ledger.
Why do debits and credits matter in SQL queries against a general ledger?
Most general ledger tables store amounts as separate debit and credit columns, or as a single signed amount depending on account type. Getting the sign convention wrong is one of the most common causes of a report that does not match the actual financial statements.
Can I connect SQL directly to QuickBooks?
QuickBooks Online does not expose a direct SQL connection. Data is typically pulled through its reporting API or through a third-party connector that replicates the data into a warehouse you can query with SQL.
What is the most common mistake when joining GL tables?
Joining a header table to a line-item table and then joining in another one-to-many table, such as tax details, without realizing it multiplies rows. This silently inflates totals unless you aggregate before joining or use a pre-aggregated subquery.
Do I need to learn a specific ERP's schema, or is general SQL enough?
General SQL skills, joins, aggregation, and window functions, transfer across every ERP. What changes between systems is table and column naming, which you can look up once you know what you are searching for.
Related Articles
External References
- SAP Help Portal, Finance Tables and General Ledger Line Items: help.sap.com
- Oracle Fusion Cloud Applications, General Ledger documentation: docs.oracle.com
0 Comments