# T-SQL patterns for ERP reporting

> Read-only SQL Server patterns for ERP reports: aging buckets, running totals, top N, calendar gaps, pivots, latest rows, fan-out, date filters and locking.

Source: https://docs.lumina-erp.com/reporting/tsql-patterns-for-erp-reporting/

**In short.** Most ERP report queries are a handful of patterns: bucket by age, total over time, rank within a group, fill in missing months and pick the latest row. Get the grain right before you join, filter dates as ranges and choose your isolation level on purpose.

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.

:::note[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](/prophet-21/reading-p21-data-with-sql/) 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.

```sql
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.

```sql
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.

```sql
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:

```sql
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:

```sql
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.

```sql
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:

```sql
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.

```sql
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:

```sql
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:

```sql
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:

```sql
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](/epicor-kinetic/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](/prophet-21/reading-p21-data-with-sql/).
- 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](/reporting/designing-an-operations-dashboard/).

## Sources

- [OVER clause (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql)
- [LAG (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql)
- [ROW_NUMBER (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql)
- [DATEDIFF (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql)
- [GENERATE_SERIES (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql)
- [DATETRUNC (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql)
- [FROM: using PIVOT and UNPIVOT, Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot)
- [SET TRANSACTION ISOLATION LEVEL (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql)
- [Table hints (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table)
- [Query hints (Transact-SQL), Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query)
- [Parameter Sensitive Plan optimization, Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization)
- [Query processing architecture guide, Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide)
- [Transaction locking and row versioning guide, Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide)

---

Epicor, Prophet 21, P21 and DynaChange are trademarks or registered trademarks of Epicor Software Corporation registered in the United States and other countries. Kinetic is a trademark of Epicor Software Corporation. Lumina ERP is an independent consultancy and is not affiliated with, sponsored by or endorsed by Epicor.