# WMS Bin Inventory and Aging SQL Generator

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

## Short answer

Build starter SQL for stock by bin, aging buckets and mixed-item bins against your own inventory table, in three SQL dialects.

## Category

developer

## Audience

- WMS analyst
- ERP developer
- Warehouse manager

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Inventory table

**Key:** inv_table

**Type:** text

**Default:** wms_inventory

**Help:** Your on-hand table or view with one row per item, bin and lot.

#### Item column

**Key:** item_col

**Type:** text

**Default:** item_no

#### Bin column

**Key:** bin_col

**Type:** text

**Default:** bin_location

#### Quantity column

**Key:** qty_col

**Type:** text

**Default:** qty_on_hand

#### Last receipt date column

**Key:** rcpt_col

**Type:** text

**Default:** last_receipt_date

#### Warehouse column

**Key:** wh_col

**Type:** text

**Default:** warehouse_id

#### Warehouse code

**Key:** wh_val

**Type:** text

**Default:** WH01

#### Slow-mover threshold (days)

**Key:** days

**Type:** number

**Default:** 180

**Blocks:**

#### Stock by bin (all dialects)

**Body:** SELECT {{bin_col}} AS bin, {{item_col}} AS item, SUM({{qty_col}}) AS qty
FROM {{inv_table}}
WHERE {{wh_col}} = '{{wh_val}}'
  AND {{qty_col}} > 0
GROUP BY {{bin_col}}, {{item_col}}
ORDER BY {{bin_col}}, {{item_col}};

#### Aging buckets (PostgreSQL)

**Body:** SELECT
  CASE
    WHEN CURRENT_DATE - {{rcpt_col}}::date <= 30 THEN '0-30 days'
    WHEN CURRENT_DATE - {{rcpt_col}}::date <= 90 THEN '31-90 days'
    WHEN CURRENT_DATE - {{rcpt_col}}::date <= {{days}} THEN '91-{{days}} days'
    ELSE 'Over {{days}} days'
  END AS age_bucket,
  COUNT(DISTINCT {{item_col}}) AS items,
  SUM({{qty_col}}) AS qty
FROM {{inv_table}}
WHERE {{wh_col}} = '{{wh_val}}' AND {{qty_col}} > 0
GROUP BY 1
ORDER BY 1;

#### Aging buckets (SQL Server)

**Body:** SELECT
  CASE
    WHEN DATEDIFF(day, {{rcpt_col}}, GETDATE()) <= 30 THEN '0-30 days'
    WHEN DATEDIFF(day, {{rcpt_col}}, GETDATE()) <= 90 THEN '31-90 days'
    WHEN DATEDIFF(day, {{rcpt_col}}, GETDATE()) <= {{days}} THEN '91-{{days}} days'
    ELSE 'Over {{days}} days'
  END AS age_bucket,
  COUNT(DISTINCT {{item_col}}) AS items,
  SUM({{qty_col}}) AS qty
FROM {{inv_table}}
WHERE {{wh_col}} = '{{wh_val}}' AND {{qty_col}} > 0
GROUP BY
  CASE
    WHEN DATEDIFF(day, {{rcpt_col}}, GETDATE()) <= 30 THEN '0-30 days'
    WHEN DATEDIFF(day, {{rcpt_col}}, GETDATE()) <= 90 THEN '31-90 days'
    WHEN DATEDIFF(day, {{rcpt_col}}, GETDATE()) <= {{days}} THEN '91-{{days}} days'
    ELSE 'Over {{days}} days'
  END
ORDER BY age_bucket;

#### Aging buckets (Oracle)

**Body:** SELECT
  CASE
    WHEN TRUNC(SYSDATE) - TRUNC({{rcpt_col}}) <= 30 THEN '0-30 days'
    WHEN TRUNC(SYSDATE) - TRUNC({{rcpt_col}}) <= 90 THEN '31-90 days'
    WHEN TRUNC(SYSDATE) - TRUNC({{rcpt_col}}) <= {{days}} THEN '91-{{days}} days'
    ELSE 'Over {{days}} days'
  END AS age_bucket,
  COUNT(DISTINCT {{item_col}}) AS items,
  SUM({{qty_col}}) AS qty
FROM {{inv_table}}
WHERE {{wh_col}} = '{{wh_val}}' AND {{qty_col}} > 0
GROUP BY
  CASE
    WHEN TRUNC(SYSDATE) - TRUNC({{rcpt_col}}) <= 30 THEN '0-30 days'
    WHEN TRUNC(SYSDATE) - TRUNC({{rcpt_col}}) <= 90 THEN '31-90 days'
    WHEN TRUNC(SYSDATE) - TRUNC({{rcpt_col}}) <= {{days}} THEN '91-{{days}} days'
    ELSE 'Over {{days}} days'
  END
ORDER BY age_bucket;

#### Mixed-item bins (all dialects)

**Body:** SELECT {{bin_col}} AS bin, COUNT(DISTINCT {{item_col}}) AS distinct_items, SUM({{qty_col}}) AS total_qty
FROM {{inv_table}}
WHERE {{wh_col}} = '{{wh_val}}' AND {{qty_col}} > 0
GROUP BY {{bin_col}}
HAVING COUNT(DISTINCT {{item_col}}) > 1
ORDER BY distinct_items DESC, {{bin_col}};

**Notes:** These queries use generic table and column names that you replace with your own. Real WMS and ERP schemas differ, often keeping lot, status and unit of measure in separate tables, so check joins, unit conversions and any quarantine or in-transit status before relying on the numbers. Run against a reporting copy first, not production.

## Content

**Intro:** Warehouse managers constantly ask the same three questions of the database: what is in each bin, how old is it, and where are we mixing items. This generator builds starter SQL for each, in PostgreSQL, SQL Server and Oracle syntax, using your own table and column names.

**Sections:**

#### How to use the generator

**Body:** Enter the table or view that holds on-hand stock by bin, then the column names for item, bin, quantity, last receipt date and warehouse. Set the slow-mover threshold in days. The page substitutes your values into each query. Copy the block for your database and run it on a reporting copy. Adjust joins if your quantity and date sit in different tables.

#### What each query returns

**Body:** The first query sums quantity by bin and item, useful for audit and slotting reviews. The aging queries group stock into buckets by days since last receipt, a rough view of slow-moving inventory. The mixed-bin query lists bins that hold more than one item, which may be intended, or may signal housekeeping or put-away problems.

**Bullets:**

- Bin and item totals for location audits
- Aging buckets to flag slow stock
- Mixed-bin list for housekeeping checks

#### Limits of days since last receipt

**Body:** Last receipt date is a proxy for age, not true age. FIFO layers, lot expiry and transfers all change the real picture. For expiry-controlled goods use lot expiry date, and for usage-based slow-moving analysis use the last issue date. Treat these queries as a first look and refine with the dates that match your policy.

#### Common mistakes

**Body:** Frequent errors are summing quantities across different units of measure, counting quarantine or in-transit stock as available, and running heavy aggregations on the live transaction database during peak hours. Always filter by warehouse, check status flags and test on a copy.

**Cta:** If you want these views as a governed dashboard or a natural-language question over your ERP, we can discuss a read-only setup that shows the query it ran.

**Faq:**

#### Will these queries work in my WMS?

**Answer:** They are generic starting points. If your WMS keeps on-hand stock in a table with item, bin, quantity and a date, they will work after you rename the columns. Most real schemas need extra joins for lot and status.

#### Why does the SQL Server query repeat the CASE in GROUP BY?

**Answer:** SQL Server does not allow a column alias in GROUP BY, so the CASE expression is repeated. PostgreSQL allows grouping by position, and the Oracle version repeats the expression like SQL Server.

#### Is last receipt date a good measure of slow-moving stock?

**Answer:** It is a quick proxy. A better measure for slow-moving stock is the last issue or sale date, or days of supply based on usage. Use receipt age for a first pass, then refine.

#### Can I run these against production?

**Answer:** Prefer a reporting replica or off-peak hours. Aggregations across the full inventory table can be heavy, and you should have read-only access. Check with your DBA before running on the live system.

## Search terms

warehouse inventory sql query, stock aging report sql, inventory by bin location sql, wms sql queries, inventory aging buckets sql server, mixed bin report query, slow moving stock sql, bin inventory query postgresql

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