Developer ToolsFree generatorPDF, Excel, Word export

WMS Bin Inventory and Aging SQL Generator

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.

Fill in

Inventory table

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

Item column

Bin column

Quantity column

Last receipt date column

Warehouse column

Warehouse code

Slow-mover threshold (days)

Generated code

Download:

Stock by bin (all dialects)

SELECT bin_location AS bin, item_no AS item, SUM(qty_on_hand) AS qty
FROM wms_inventory
WHERE warehouse_id = 'WH01'
  AND qty_on_hand > 0
GROUP BY bin_location, item_no
ORDER BY bin_location, item_no;

Aging buckets (PostgreSQL)

SELECT
  CASE
    WHEN CURRENT_DATE - last_receipt_date::date <= 30 THEN '0-30 days'
    WHEN CURRENT_DATE - last_receipt_date::date <= 90 THEN '31-90 days'
    WHEN CURRENT_DATE - last_receipt_date::date <= 180 THEN '91-180 days'
    ELSE 'Over 180 days'
  END AS age_bucket,
  COUNT(DISTINCT item_no) AS items,
  SUM(qty_on_hand) AS qty
FROM wms_inventory
WHERE warehouse_id = 'WH01' AND qty_on_hand > 0
GROUP BY 1
ORDER BY 1;

Aging buckets (SQL Server)

SELECT
  CASE
    WHEN DATEDIFF(day, last_receipt_date, GETDATE()) <= 30 THEN '0-30 days'
    WHEN DATEDIFF(day, last_receipt_date, GETDATE()) <= 90 THEN '31-90 days'
    WHEN DATEDIFF(day, last_receipt_date, GETDATE()) <= 180 THEN '91-180 days'
    ELSE 'Over 180 days'
  END AS age_bucket,
  COUNT(DISTINCT item_no) AS items,
  SUM(qty_on_hand) AS qty
FROM wms_inventory
WHERE warehouse_id = 'WH01' AND qty_on_hand > 0
GROUP BY
  CASE
    WHEN DATEDIFF(day, last_receipt_date, GETDATE()) <= 30 THEN '0-30 days'
    WHEN DATEDIFF(day, last_receipt_date, GETDATE()) <= 90 THEN '31-90 days'
    WHEN DATEDIFF(day, last_receipt_date, GETDATE()) <= 180 THEN '91-180 days'
    ELSE 'Over 180 days'
  END
ORDER BY age_bucket;

Aging buckets (Oracle)

SELECT
  CASE
    WHEN TRUNC(SYSDATE) - TRUNC(last_receipt_date) <= 30 THEN '0-30 days'
    WHEN TRUNC(SYSDATE) - TRUNC(last_receipt_date) <= 90 THEN '31-90 days'
    WHEN TRUNC(SYSDATE) - TRUNC(last_receipt_date) <= 180 THEN '91-180 days'
    ELSE 'Over 180 days'
  END AS age_bucket,
  COUNT(DISTINCT item_no) AS items,
  SUM(qty_on_hand) AS qty
FROM wms_inventory
WHERE warehouse_id = 'WH01' AND qty_on_hand > 0
GROUP BY
  CASE
    WHEN TRUNC(SYSDATE) - TRUNC(last_receipt_date) <= 30 THEN '0-30 days'
    WHEN TRUNC(SYSDATE) - TRUNC(last_receipt_date) <= 90 THEN '31-90 days'
    WHEN TRUNC(SYSDATE) - TRUNC(last_receipt_date) <= 180 THEN '91-180 days'
    ELSE 'Over 180 days'
  END
ORDER BY age_bucket;

Mixed-item bins (all dialects)

SELECT bin_location AS bin, COUNT(DISTINCT item_no) AS distinct_items, SUM(qty_on_hand) AS total_qty
FROM wms_inventory
WHERE warehouse_id = 'WH01' AND qty_on_hand > 0
GROUP BY bin_location
HAVING COUNT(DISTINCT item_no) > 1
ORDER BY distinct_items DESC, bin_location;

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.

Ask your warehouse data in plain English

Want to ask questions like these without writing SQL? We can discuss a read-only natural-language layer over your ERP or WMS data.

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

How to use the generator

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

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.

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

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

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.

Frequently Asked Questions

Will these queries work in my WMS?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools