# Inventory Aging Buckets SQL Generator

> Source: https://netray.co/tools/inventory-aging-buckets-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build an inventory aging query with your own table and column names and bucket sizes, ready to paste into SQL Server style SQL.

## Category

developer

## Audience

- ERP analyst
- Report developer
- Controller
- Database administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Inventory layer or lot table

**Key:** table

**Type:** text

**Default:** inventory_lots

#### Item column

**Key:** item

**Type:** text

**Default:** item_no

#### On hand quantity column

**Key:** qty

**Type:** text

**Default:** qty_on_hand

#### Unit cost column

**Key:** cost

**Type:** text

**Default:** unit_cost

#### Receipt date column

**Key:** recv

**Type:** text

**Default:** receipt_date

#### Plant or warehouse column

**Key:** plant

**Type:** text

**Default:** plant_code

#### As of date

**Key:** asof

**Type:** date

**Default:** 2026-10-31

#### Bucket 1 limit (days)

**Key:** b1

**Type:** number

**Default:** 30

#### Bucket 2 limit (days)

**Key:** b2

**Type:** number

**Default:** 90

#### Bucket 3 limit (days)

**Key:** b3

**Type:** number

**Default:** 180

#### Bucket 4 limit (days)

**Key:** b4

**Type:** number

**Default:** 365

**Blocks:**

#### Inventory value by age bucket

**Body:** -- 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}} AS plant,
    {{item}} AS item,
    SUM({{qty}} * {{cost}}) AS total_value,
    SUM(CASE WHEN DATEDIFF(day, {{recv}}, '{{asof}}') <= {{b1}} THEN {{qty}} * {{cost}} ELSE 0 END) AS age_0_{{b1}},
    SUM(CASE WHEN DATEDIFF(day, {{recv}}, '{{asof}}') > {{b1}} AND DATEDIFF(day, {{recv}}, '{{asof}}') <= {{b2}} THEN {{qty}} * {{cost}} ELSE 0 END) AS age_{{b1}}_{{b2}},
    SUM(CASE WHEN DATEDIFF(day, {{recv}}, '{{asof}}') > {{b2}} AND DATEDIFF(day, {{recv}}, '{{asof}}') <= {{b3}} THEN {{qty}} * {{cost}} ELSE 0 END) AS age_{{b2}}_{{b3}},
    SUM(CASE WHEN DATEDIFF(day, {{recv}}, '{{asof}}') > {{b3}} AND DATEDIFF(day, {{recv}}, '{{asof}}') <= {{b4}} THEN {{qty}} * {{cost}} ELSE 0 END) AS age_{{b3}}_{{b4}},
    SUM(CASE WHEN DATEDIFF(day, {{recv}}, '{{asof}}') > {{b4}} THEN {{qty}} * {{cost}} ELSE 0 END) AS age_over_{{b4}}
FROM {{table}}
WHERE {{qty}} > 0
  AND {{recv}} <= '{{asof}}'
GROUP BY {{plant}}, {{item}}
ORDER BY age_over_{{b4}} DESC;

#### Summary by plant

**Body:** SELECT
    {{plant}} AS plant,
    SUM({{qty}} * {{cost}}) AS total_value,
    SUM(CASE WHEN DATEDIFF(day, {{recv}}, '{{asof}}') > {{b4}} THEN {{qty}} * {{cost}} ELSE 0 END) AS value_over_{{b4}}_days,
    100.0 * SUM(CASE WHEN DATEDIFF(day, {{recv}}, '{{asof}}') > {{b4}} THEN {{qty}} * {{cost}} ELSE 0 END)
        / NULLIF(SUM({{qty}} * {{cost}}), 0) AS pct_over_{{b4}}_days
FROM {{table}}
WHERE {{qty}} > 0
  AND {{recv}} <= '{{asof}}'
GROUP BY {{plant}}
ORDER BY pct_over_{{b4}}_days DESC;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Will this work on my ERP database?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

inventory aging report sql, inventory aging query, aged inventory sql, stock ageing buckets sql, inventory aging report erp, excess obsolete inventory query, sql server inventory aging

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json