Developer ToolsFree generatorPDF, Excel, Word export

Inventory Aging Buckets SQL Generator

An inventory aging report shows how much stock value is old, which is the first screen anyone opens when asked about excess and obsolete inventory. This generator writes the bucketed SQL for your own table and column names.

Fill in

Inventory layer or lot table

Item column

On hand quantity column

Unit cost column

Receipt date column

Plant or warehouse column

As of date

Bucket 1 limit (days)

Bucket 2 limit (days)

Bucket 3 limit (days)

Bucket 4 limit (days)

Generated code

Download:

Inventory value by age bucket

-- Generic names. Replace table and columns with your ERP's equivalents.
-- SQL Server style DATEDIFF. Adapt date arithmetic for Oracle, PostgreSQL or MySQL.
SELECT
    plant_code AS plant,
    item_no AS item,
    SUM(qty_on_hand * unit_cost) AS total_value,
    SUM(CASE WHEN DATEDIFF(day, receipt_date, '2026-10-31') <= 30 THEN qty_on_hand * unit_cost ELSE 0 END) AS age_0_30,
    SUM(CASE WHEN DATEDIFF(day, receipt_date, '2026-10-31') > 30 AND DATEDIFF(day, receipt_date, '2026-10-31') <= 90 THEN qty_on_hand * unit_cost ELSE 0 END) AS age_30_90,
    SUM(CASE WHEN DATEDIFF(day, receipt_date, '2026-10-31') > 90 AND DATEDIFF(day, receipt_date, '2026-10-31') <= 180 THEN qty_on_hand * unit_cost ELSE 0 END) AS age_90_180,
    SUM(CASE WHEN DATEDIFF(day, receipt_date, '2026-10-31') > 180 AND DATEDIFF(day, receipt_date, '2026-10-31') <= 365 THEN qty_on_hand * unit_cost ELSE 0 END) AS age_180_365,
    SUM(CASE WHEN DATEDIFF(day, receipt_date, '2026-10-31') > 365 THEN qty_on_hand * unit_cost ELSE 0 END) AS age_over_365
FROM inventory_lots
WHERE qty_on_hand > 0
  AND receipt_date <= '2026-10-31'
GROUP BY plant_code, item_no
ORDER BY age_over_365 DESC;

Summary by plant

SELECT
    plant_code AS plant,
    SUM(qty_on_hand * unit_cost) AS total_value,
    SUM(CASE WHEN DATEDIFF(day, receipt_date, '2026-10-31') > 365 THEN qty_on_hand * unit_cost ELSE 0 END) AS value_over_365_days,
    100.0 * SUM(CASE WHEN DATEDIFF(day, receipt_date, '2026-10-31') > 365 THEN qty_on_hand * unit_cost ELSE 0 END)
        / NULLIF(SUM(qty_on_hand * unit_cost), 0) AS pct_over_365_days
FROM inventory_lots
WHERE qty_on_hand > 0
  AND receipt_date <= '2026-10-31'
GROUP BY plant_code
ORDER BY pct_over_365_days DESC;

Table and column names are generic placeholders, not the schema of a specific ERP. Many ERPs do not keep receipt date per quantity layer, so age may need to come from FIFO layers, lot receipt dates or last movement date. Verify against a known item before relying on totals, and test on a non production copy.

Ask your ERP for aged stock in plain English

Optional: see how a read-only question layer can answer aging questions without writing SQL.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter the table that holds quantity with a date per layer or lot, the column names, the as of date and your bucket limits. Copy the generated query into your SQL client, run it on a reporting copy and compare the total to the inventory valuation report. If the totals differ, the table you chose does not cover all stock, or the cost column is not the valuation cost.

Check the total against the GL inventory balance
Run on a reporting replica where possible
Change bucket limits to match your reserve policy

What age means

Age can be measured from the receipt date, the last movement date, or the last issue to production or sales. For obsolescence, days since last issue is often more useful than days since receipt, because a part received a year ago and used weekly is not obsolete. Change the date column in the generator to test each definition.

Reading the output

Sort by the oldest bucket to find the value at risk, then review those items against open demand, bills of material and engineering change status. The plant summary shows the percentage over the final bucket, which is a simple trend KPI month on month.

Common mistakes

Using list or standard cost where the valuation uses a different basis, including consigned or customer owned stock, and ignoring unit of measure differences all skew totals. Also check time zones and date types when comparing dates in the as of filter.

Frequently Asked Questions

Will this work on my ERP database?

It works on any SQL database once table and column names are replaced and the date function is adapted. It does not assume a specific ERP schema, and many ERPs expose aging through a standard report you should check first.

Why does my total differ from the valuation report?

Usual causes are stock in other tables such as in transit or consigned, a different cost basis, a different as of date, or layers that already have zero quantity. Reconcile by plant and item for a few examples.

Can I change the number of buckets?

The generator uses four limits and an over bucket. To add buckets, copy a CASE line in the output and add another limit. Keep the boundaries consistent so no days fall between buckets.

Is it safe to run on production?

The query only reads data, but a large scan can slow a busy system. Run it off hours or on a reporting copy, and have your database administrator review it first.

If aging analysis is a recurring request, Netray can connect a read-only layer to your ERP so you can ask for aged stock in plain language and see the query behind the answer.

Talk to an Expert Explore More Free Tools

Related Tools

ERP Operations

More Developer Tools tools

See all

Back to All Tools