T-SQL patterns for ERP reporting

Most report queries against an ERP database come down to a short list of patterns: put open balances into age buckets, total a measure over time, rank items within a customer, show months with no activity and pick the most recent row from a history table. Below is a read-only T-SQL version of each for Microsoft SQL Server. Three habits decide whether the result is right and fast: aggregate before you join, filter dates as ranges and choose an isolation level on purpose.

Invented tables, not a real ERP schema

Every table and column on this page is invented for illustration: customer, item, sales_invoice_header, sales_invoice_line, ar_open_item and item_cost_history. They are not the schema of Prophet 21 software, Kinetic software or any other product. Map each pattern onto your own tables, and check flags, units and currencies in your own system before trusting a total.

Before you start

• Use a read-only login. Every query here is a SELECT. See Reading Prophet 21 data with SQL, safely for how we set one up. The approach is the same for any ERP on SQL Server.
• Know your version. Window functions with ROWS and RANGE frames need SQL Server 2012 or later. GENERATE_SERIES and DATETRUNC need SQL Server 2022, and GENERATE_SERIES also needs database compatibility level 160. Run SELECT @@VERSION; and check the compatibility level before you copy a pattern.
• If your ERP is hosted, direct SQL access depends on your agreement with the host.

The invented tables

Table	One row per	Columns used here
customer	Customer	customer_id, customer_name
item	Item	item_id, item_desc
sales_invoice_header	Invoice	invoice_no, customer_id, invoice_date, freight_amount
sales_invoice_line	Invoice line	invoice_no, line_no, item_id, qty_shipped, extended_price, extended_cost
ar_open_item	Open receivable document	customer_id, invoice_no, due_date, amount_open
item_cost_history	Cost change	cost_history_id, item_id, effective_date, unit_cost

Patterns at a glance

Question	Pattern	Key function
How old is what customers owe us?	Aging buckets	CASE over DATEDIFF
What are sales to date, and versus last month?	Running totals and period over period	SUM() OVER, LAG
What are each customer's top five items?	Top N per group	ROW_NUMBER
Which months had no sales?	Date spine	Calendar table or GENERATE_SERIES
One column per month	Pivot	Conditional aggregation or PIVOT
What is the current cost of each item?	Latest row per key	ROW_NUMBER or OUTER APPLY
Why is freight doubled?	Avoiding fan-out	Aggregate before the join

Aging buckets with CASE and DATEDIFF

An aging report sorts open balances into buckets by days past due. Compute the days once, then let each bucket be a conditional sum.

DECLARE @as_of date = '2026-09-30';

SELECT c.customer_id,
       c.customer_name,
       SUM(CASE WHEN d.days_past_due <= 0 THEN o.amount_open ELSE 0 END) AS current_due,
       SUM(CASE WHEN d.days_past_due BETWEEN 1 AND 30 THEN o.amount_open ELSE 0 END) AS past_due_1_30,
       SUM(CASE WHEN d.days_past_due BETWEEN 31 AND 60 THEN o.amount_open ELSE 0 END) AS past_due_31_60,
       SUM(CASE WHEN d.days_past_due BETWEEN 61 AND 90 THEN o.amount_open ELSE 0 END) AS past_due_61_90,
       SUM(CASE WHEN d.days_past_due > 90 THEN o.amount_open ELSE 0 END) AS past_due_over_90,
       SUM(o.amount_open) AS total_open
FROM dbo.ar_open_item AS o
JOIN dbo.customer AS c
  ON c.customer_id = o.customer_id
CROSS APPLY (SELECT DATEDIFF(day, o.due_date, @as_of) AS days_past_due) AS d
WHERE o.amount_open <> 0
GROUP BY c.customer_id, c.customer_name
ORDER BY total_open DESC;

Notes on doing this well:

• Buckets must not overlap or leave gaps. Check that the bucket columns add up to total_open on every row. If they do not, a boundary is wrong.
• Age in days, not months. DATEDIFF counts the date part boundaries crossed between two values, not elapsed time. DATEDIFF(month, '2026-01-31', '2026-02-01') returns 1 even though one day passed. Day boundaries are the ones you want for aging.
• Due date or invoice date? Aging by due date answers "how late is it", and aging by invoice date answers "how old is it". Label the report with the basis you chose, and match what finance uses.
• Aging as of a past date is harder. amount_open is today's balance. Aging as of last month end needs the balance as of that date, rebuilt from documents and payments dated on or before it. Reconcile to the receivables control account for that period before anyone relies on it.
• Credits and unapplied cash are open items too. Decide whether they reduce the bucket they fall in or appear in their own column.

Running totals and period over period

Summarize to one row per month first, then apply window functions to the monthly rows. Windows over raw invoice lines are slower and harder to read.

DECLARE @from date = '2025-01-01';
DECLARE @to   date = '2026-10-01';

WITH monthly AS (
    SELECT DATEFROMPARTS(YEAR(h.invoice_date), MONTH(h.invoice_date), 1) AS month_start,
           SUM(l.extended_price) AS sales
    FROM dbo.sales_invoice_header AS h
    JOIN dbo.sales_invoice_line AS l
      ON l.invoice_no = h.invoice_no
    WHERE h.invoice_date >= @from
      AND h.invoice_date < @to
    GROUP BY DATEFROMPARTS(YEAR(h.invoice_date), MONTH(h.invoice_date), 1)
)
SELECT month_start,
       sales,
       SUM(sales) OVER (PARTITION BY YEAR(month_start)
                        ORDER BY month_start
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sales_ytd,
       LAG(sales, 1)  OVER (ORDER BY month_start) AS sales_prior_month,
       LAG(sales, 12) OVER (ORDER BY month_start) AS sales_same_month_last_year,
       sales - LAG(sales, 12) OVER (ORDER BY month_start) AS change_vs_last_year
FROM monthly
ORDER BY month_start;

Three details matter:

• Write the frame explicitly. When an aggregate has ORDER BY in its OVER clause and no frame, SQL Server uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as one step, so a running total over daily rows with ties jumps by the whole day at once. ROWS counts physical rows. State the one you mean.
• LAG counts rows, not months. LAG(sales, 12) is "12 rows back". If a month has no sales, there is no row for it, and the comparison shifts without warning. Build the month list from a date spine (next section) before using LAG for period comparisons.
• DATEFROMPARTS works on every current version. On SQL Server 2022 and later, DATETRUNC(month, h.invoice_date) does the same job.

Top N per group with ROW_NUMBER

"Top five items per customer" cannot be done with TOP (5), which limits the whole result. Number the rows within each customer and keep the first five.

DECLARE @from date = '2026-01-01';
DECLARE @to   date = '2027-01-01';

WITH item_sales AS (
    SELECT h.customer_id,
           l.item_id,
           SUM(l.extended_price) AS sales
    FROM dbo.sales_invoice_header AS h
    JOIN dbo.sales_invoice_line AS l
      ON l.invoice_no = h.invoice_no
    WHERE h.invoice_date >= @from
      AND h.invoice_date < @to
    GROUP BY h.customer_id, l.item_id
),
ranked AS (
    SELECT customer_id,
           item_id,
           sales,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY sales DESC, item_id) AS rn
    FROM item_sales
)
SELECT r.customer_id,
       c.customer_name,
       r.rn AS item_rank,
       r.item_id,
       i.item_desc,
       r.sales
FROM ranked AS r
JOIN dbo.customer AS c ON c.customer_id = r.customer_id
JOIN dbo.item AS i ON i.item_id = r.item_id
WHERE r.rn <= 5
ORDER BY c.customer_name, r.rn;

The item_id after sales DESC is a tie-breaker. Without it, two items with equal sales can swap places between runs, which makes a report look unstable. If ties should share a rank, use RANK or DENSE_RANK instead and accept that a group may return more than five rows.

A date spine for months with no sales

Grouping only returns months that have rows. A customer who bought nothing in March has no March row, so a chart skips the month and a 12-month average divides by 11. Fix it by starting from a list of months and left-joining the data onto it.

The durable fix is a permanent calendar table, one row per day, with columns for month start, fiscal period, fiscal year and working day. It is also the place to hold your fiscal calendar, which rarely matches the calendar month. If you cannot create tables in the ERP database, keep it in a separate reporting database.

Without a calendar table, generate the months in the query. On SQL Server 2022 and later at compatibility level 160:

DECLARE @first_month date = '2026-01-01';
DECLARE @months int = 12;

WITH months AS (
    SELECT DATEADD(month, s.value, @first_month) AS month_start
    FROM GENERATE_SERIES(0, @months - 1) AS s
),
monthly AS (
    SELECT h.customer_id,
           DATEFROMPARTS(YEAR(h.invoice_date), MONTH(h.invoice_date), 1) AS month_start,
           SUM(l.extended_price) AS sales
    FROM dbo.sales_invoice_header AS h
    JOIN dbo.sales_invoice_line AS l
      ON l.invoice_no = h.invoice_no
    WHERE h.invoice_date >= @first_month
      AND h.invoice_date < DATEADD(month, @months, @first_month)
    GROUP BY h.customer_id,
             DATEFROMPARTS(YEAR(h.invoice_date), MONTH(h.invoice_date), 1)
)
SELECT c.customer_id,
       c.customer_name,
       m.month_start,
       COALESCE(s.sales, 0) AS sales
FROM dbo.customer AS c
CROSS JOIN months AS m
LEFT JOIN monthly AS s
  ON s.customer_id = c.customer_id
 AND s.month_start = m.month_start
ORDER BY c.customer_name, m.month_start;

On older versions, replace the months expression with a small numbers list:

DECLARE @first_month date = '2026-01-01';

WITH n AS (
    SELECT v.n
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) AS v(n)
),
months AS (
    SELECT DATEADD(month, n.n, @first_month) AS month_start
    FROM n
)
SELECT month_start
FROM months;

The CROSS JOIN produces every customer for every month, so restrict customer to the customers you care about (active, in a territory, with sales in the period) before the cross join, or the result grows quickly.

Pivot for monthly columns

Finance often wants one row per customer with a column per month. The clearest way in T-SQL is conditional aggregation, which also lets you add a total column in the same pass.

DECLARE @year int = 2026;

SELECT c.customer_id,
       c.customer_name,
       SUM(CASE WHEN MONTH(h.invoice_date) = 1  THEN l.extended_price ELSE 0 END) AS sales_jan,
       SUM(CASE WHEN MONTH(h.invoice_date) = 2  THEN l.extended_price ELSE 0 END) AS sales_feb,
       SUM(CASE WHEN MONTH(h.invoice_date) = 3  THEN l.extended_price ELSE 0 END) AS sales_mar,
       SUM(CASE WHEN MONTH(h.invoice_date) = 12 THEN l.extended_price ELSE 0 END) AS sales_dec,
       SUM(l.extended_price) AS year_total
FROM dbo.sales_invoice_header AS h
JOIN dbo.sales_invoice_line AS l
  ON l.invoice_no = h.invoice_no
JOIN dbo.customer AS c
  ON c.customer_id = h.customer_id
WHERE h.invoice_date >= DATEFROMPARTS(@year, 1, 1)
  AND h.invoice_date < DATEFROMPARTS(@year + 1, 1, 1)
GROUP BY c.customer_id, c.customer_name
ORDER BY year_total DESC;

The months April to November are left out above to keep the example short. Add them the same way. The same result with the PIVOT operator:

DECLARE @year int = 2026;

WITH src AS (
    SELECT h.customer_id,
           MONTH(h.invoice_date) AS mo,
           l.extended_price
    FROM dbo.sales_invoice_header AS h
    JOIN dbo.sales_invoice_line AS l
      ON l.invoice_no = h.invoice_no
    WHERE h.invoice_date >= DATEFROMPARTS(@year, 1, 1)
      AND h.invoice_date < DATEFROMPARTS(@year + 1, 1, 1)
)
SELECT p.customer_id,
       COALESCE(p.[1], 0)  AS sales_jan,
       COALESCE(p.[2], 0)  AS sales_feb,
       COALESCE(p.[3], 0)  AS sales_mar,
       COALESCE(p.[12], 0) AS sales_dec
FROM src
PIVOT (SUM(extended_price) FOR mo IN ([1], [2], [3], [12])) AS p;

PIVOT needs the column list written out, and it returns NULL rather than zero for empty cells. Every column in the source that is not aggregated or pivoted becomes a grouping column, which is why the source is narrowed in a CTE first. If the columns change with the data, such as "the last 13 months", let the reporting tool pivot a long result (one row per customer per month) rather than building dynamic SQL.

Deduplicating to the latest row

History tables hold many rows per key: cost changes, price changes, status changes, addresses. Most reports want one row per key, either the latest or the one in effect on a date.

DECLARE @as_of date = '2026-09-30';

WITH ranked AS (
    SELECT ch.item_id,
           ch.effective_date,
           ch.unit_cost,
           ROW_NUMBER() OVER (PARTITION BY ch.item_id
                              ORDER BY ch.effective_date DESC, ch.cost_history_id DESC) AS rn
    FROM dbo.item_cost_history AS ch
    WHERE ch.effective_date <= @as_of
)
SELECT item_id,
       effective_date,
       unit_cost
FROM ranked
WHERE rn = 1;

The second sort key breaks ties when two changes share a date. Pick a column that increases with each change. When you need the latest row for a short list of keys rather than all of them, OUTER APPLY with TOP (1) reads less:

SELECT i.item_id,
       i.item_desc,
       lc.effective_date,
       lc.unit_cost
FROM dbo.item AS i
OUTER APPLY (
    SELECT TOP (1) ch.effective_date, ch.unit_cost
    FROM dbo.item_cost_history AS ch
    WHERE ch.item_id = i.item_id
      AND ch.effective_date <= @as_of
    ORDER BY ch.effective_date DESC, ch.cost_history_id DESC
) AS lc
WHERE i.item_id IN ('ITEM-100', 'ITEM-200');

Avoid SELECT DISTINCT as a way to "remove duplicates". It removes identical rows, and rows from a history table are rarely identical, so it hides the problem without fixing it.

Avoiding fan-out: aggregate before you join

A header value repeats on every line when you join header to lines. Sum it after the join and it is counted once per line. This is the most common reason an ERP report total is too high, and it looks right on a single-line invoice.

This query overstates freight on every invoice with more than one line:

SELECT h.customer_id,
       SUM(l.extended_price) AS sales,
       SUM(h.freight_amount) AS freight
FROM dbo.sales_invoice_header AS h
JOIN dbo.sales_invoice_line AS l
  ON l.invoice_no = h.invoice_no
GROUP BY h.customer_id;

Reduce the lines to one row per invoice first, then join to the header, so every value sits at the same grain:

WITH line_totals AS (
    SELECT l.invoice_no,
           SUM(l.extended_price) AS sales,
           SUM(l.extended_cost)  AS cost
    FROM dbo.sales_invoice_line AS l
    GROUP BY l.invoice_no
)
SELECT h.customer_id,
       SUM(lt.sales) AS sales,
       SUM(lt.cost)  AS cost,
       SUM(h.freight_amount) AS freight
FROM dbo.sales_invoice_header AS h
JOIN line_totals AS lt
  ON lt.invoice_no = h.invoice_no
GROUP BY h.customer_id;

The same rule applies one level down. Join lines to a detail table such as shipments, lots or serial numbers, and every line value repeats per detail row. We explain grain in more depth in BAQ fundamentals, and the idea is the same in any SQL.

A quick test for fan-out: run COUNT(*) and COUNT(DISTINCT key) at the grain you expect. If they differ, a join is multiplying rows.

SARGable date filters and parameter sniffing

Filter dates as half-open ranges

A predicate is SARGable when SQL Server can use it to seek an index. Wrapping the column in a function usually prevents that, so the server reads every row and evaluates the function on each one.

Avoid	Use instead
WHERE YEAR(invoice_date) = 2026	WHERE invoice_date >= '2026-01-01' AND invoice_date < '2027-01-01'
WHERE CONVERT(date, invoice_date) = @day	WHERE invoice_date >= @day AND invoice_date < DATEADD(day, 1, @day)
WHERE invoice_date BETWEEN @from AND @to on a column with times	WHERE invoice_date >= @from AND invoice_date < DATEADD(day, 1, @to)
WHERE DATEDIFF(day, invoice_date, GETDATE()) <= 30	WHERE invoice_date >= DATEADD(day, -30, CAST(GETDATE() AS date))

The half-open form, greater than or equal to the start and less than the day after the end, is correct whether the column holds a date or a date and time. BETWEEN on a datetime column drops everything after midnight on the last day, with no error.

Parameter sniffing

SQL Server compiles a parameterized query or stored procedure once and reuses the plan. The plan is built for the parameter values of the first call. For report queries that is often a problem, because a plan built for a one-day range can be poor for a five-year range, and the other way round. The symptom is a report that is fast for one user and slow for another, or fast yesterday and slow today with no change.

Options, in the order we try them:

1. Check the filter is SARGable and the index exists. A plan that has to scan is slow for every parameter value, which is a different problem.
2. Add OPTION (RECOMPILE) to the report query. The statement is compiled for the actual values each time it runs. For a report that runs a few times an hour, the compile cost is small next to the query cost. It is a poor choice for a statement that runs thousands of times a minute.
3. Use OPTIMIZE FOR when one value is representative of most runs and you want the plan built for it.
4. Split very different shapes into separate queries, for example a detail query for short ranges and a summary query for long ones.

SQL Server 2022 added Parameter Sensitive Plan optimization, which can keep several cached plans for one parameterized statement at compatibility level 160. Microsoft documents that it currently works only with equality predicates, so it does not help the date range filters that most reports use.

READ COMMITTED, row versioning and NOLOCK

SQL Server's default isolation level is read committed. Under the default locking behavior, a reader takes short shared locks, so a long report can wait on writers and writers can wait on it. On a busy ERP database, users see that as saves hanging.

Choice	What the report sees	Cost
Read committed, locking (the default when row versioning is off)	Only committed data	Can block, and be blocked by, order entry and posting
Read committed snapshot (a database option)	Committed data as of the start of each statement, without shared locks	Version store in tempdb, and a database-wide change the DBA and ERP vendor must approve
Snapshot isolation (a database option, then per session)	Committed data as of the start of the transaction, consistent across several statements	Same version store cost, and the session must request it
NOLOCK or read uncommitted	Uncommitted data, and rows can be missed or read twice while pages move	No shared locks, but wrong numbers are possible

Microsoft documents that NOLOCK is the same as READUNCOMMITTED, that it allows dirty reads and that a reader can see records twice or not at all. It also does not avoid every lock, because queries still take schema stability locks.

Our guidance:

• Check what your database already does. If read committed snapshot is on, a plain query already reads without shared locks and NOLOCK buys little. See the sys.databases query in Reading Prophet 21 data with SQL, safely.
• Never use NOLOCK on numbers finance will reconcile. A total that includes an uncommitted line, or counts a moved row twice, is not reproducible.
• For a multi-statement report that must agree with itself, such as a summary and its detail, snapshot isolation gives each statement the same point in time, if the database allows it.
• Move heavy recurring reports off the live database to a replica, a restored copy or a warehouse. That removes the trade-off rather than managing it.

Before you publish a query

1. State the grain of the result in one sentence, and check every summed column lives at that grain.
2. Reconcile one number to a screen or standard report the business already trusts.
3. Test the edges: a month with no sales, an invoice with many lines, a credit memo, a customer in another currency.
4. Read the actual execution plan for scans on large tables and for warnings.
5. Save the query in version control with a note on what it answers and which ERP version it was checked against.

If the query feeds a dashboard, agree the metric definition first. See Designing an operations dashboard.