# SQL Days of Supply and Inventory Coverage Query Generator

> Source: https://netray.co/tools/sql-days-of-supply-and-coverage-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and column names to produce a days of supply and below-safety-stock SQL query for any relational ERP database.

## Category

developer

## Audience

- ERP developer
- Inventory analyst
- BI developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Item stock table

**Key:** item_table

**Type:** text

**Default:** item_stock

#### Item column

**Key:** item_col

**Type:** text

**Default:** item_no

#### Site column

**Key:** site_col

**Type:** text

**Default:** site_id

#### On hand quantity column

**Key:** onhand_col

**Type:** text

**Default:** qty_on_hand

#### Safety stock column

**Key:** safety_col

**Type:** text

**Default:** safety_stock_qty

#### Usage history table

**Key:** usage_table

**Type:** text

**Default:** inventory_transactions

#### Usage quantity column

**Key:** usage_qty

**Type:** text

**Default:** qty

#### Usage date column

**Key:** usage_date

**Type:** text

**Default:** txn_date

#### Transaction type column

**Key:** usage_type

**Type:** text

**Default:** txn_type

#### Issue transaction type value

**Key:** usage_value

**Type:** text

**Default:** ISSUE

#### Usage window in days

**Key:** days

**Type:** number

**Default:** 90

#### Site filter

**Key:** site

**Type:** text

**Default:** PLANT01

**Blocks:**

#### Days of supply by item

**Body:** -- Generic SQL (ANSI style). Replace table and column names with your own.
-- Date arithmetic differs by database: adjust the window filter below.
WITH usage AS (
  SELECT {{item_col}}, SUM({{usage_qty}}) AS total_issued,
         SUM({{usage_qty}}) / {{days}}.0 AS avg_daily_usage
  FROM {{usage_table}}
  WHERE {{usage_type}} = '{{usage_value}}'
    AND {{usage_date}} >= CURRENT_DATE - INTERVAL '{{days}}' DAY
  GROUP BY {{item_col}}
)
SELECT s.{{item_col}},
       s.{{site_col}},
       s.{{onhand_col}} AS on_hand,
       s.{{safety_col}} AS safety_stock,
       COALESCE(u.avg_daily_usage, 0) AS avg_daily_usage,
       CASE WHEN COALESCE(u.avg_daily_usage, 0) = 0 THEN NULL
            ELSE s.{{onhand_col}} / u.avg_daily_usage END AS days_of_supply,
       CASE WHEN s.{{onhand_col}} < s.{{safety_col}} THEN 'BELOW SAFETY' ELSE 'OK' END AS status
FROM {{item_table}} s
LEFT JOIN usage u ON u.{{item_col}} = s.{{item_col}}
WHERE s.{{site_col}} = '{{site}}'
ORDER BY days_of_supply ASC;

#### Items below safety stock only

**Body:** SELECT s.{{item_col}}, s.{{site_col}}, s.{{onhand_col}}, s.{{safety_col}},
       s.{{safety_col}} - s.{{onhand_col}} AS shortfall
FROM {{item_table}} s
WHERE s.{{site_col}} = '{{site}}'
  AND s.{{safety_col}} > 0
  AND s.{{onhand_col}} < s.{{safety_col}}
ORDER BY shortfall DESC;

#### Excess coverage (more than 365 days)

**Body:** SELECT s.{{item_col}}, s.{{onhand_col}},
       s.{{onhand_col}} / (u.total_issued / {{days}}.0) AS days_of_supply
FROM {{item_table}} s
JOIN (SELECT {{item_col}}, SUM({{usage_qty}}) AS total_issued
      FROM {{usage_table}}
      WHERE {{usage_type}} = '{{usage_value}}'
        AND {{usage_date}} >= CURRENT_DATE - INTERVAL '{{days}}' DAY
      GROUP BY {{item_col}}) u
  ON u.{{item_col}} = s.{{item_col}}
WHERE u.total_issued > 0
  AND s.{{onhand_col}} / (u.total_issued / {{days}}.0) > 365;

**Notes:** Names are generic placeholders, not a specific ERP schema. Verify date syntax (SQL Server uses DATEADD, Oracle uses SYSDATE - n, MySQL uses DATE_SUB), whether quantities are signed, and that scrap or transfer transactions are excluded from usage.

## Content

**Intro:** Days of supply is the quickest check on whether stock matches demand. This generator writes the SQL for it from your own table and column names, so you can run it in your ERP reporting database or hand it to a BI developer.

**Sections:**

#### How to use the generator

**Body:** Type the names of your stock table, usage history table and the key columns. Pick a usage window, usually 90 days for fast items and 365 for slow items. The three blocks give a full coverage list, a below-safety-stock list and an excess list. Copy the code into your SQL client and test it on a small site first.

**Bullets:**

- Use read-only database access or a reporting replica
- Start with one site to check that results look right
- Export the result to Excel for the planning meeting

#### The logic

**Body:** Average daily usage is the sum of issues over the window divided by the number of days. Days of supply is on hand divided by that rate. Division by zero is handled by returning NULL when there was no usage, so slow and dead items do not break the report. The status column compares on hand with the safety stock field and flags items below it.

#### Reading the results

**Body:** Days of supply well below the replenishment lead time signals risk of a stockout. Values above a year point to excess or obsolete stock. Look at both ends of the sorted list together, because the same planning rule may be causing short and long positions in different items.

#### Common mistakes

**Body:** Including receipts, returns or transfers in usage inflates demand. A window that crosses a plant shutdown understates it. Mixing units of measure between stock and usage tables gives nonsense coverage. Always check a handful of items by hand against the ERP screen.

**Cta:** If you prefer to ask for coverage in plain language, Netray's ERPray can run read-only queries like this against your ERP data and show the SQL it used.

**Faq:**

#### Which tables does this work with?

**Answer:** Any relational table pair with an on hand quantity and a transaction history. The names are placeholders you replace, so it fits SQL Server, Oracle, PostgreSQL or MySQL ERP databases after minor date syntax changes.

#### How long should the usage window be?

**Answer:** Ninety days suits fast movers. Slow items need 12 months or more, otherwise most show zero usage. Many teams run the report twice with different windows and compare.

#### Why is days of supply NULL for some items?

**Answer:** Those items had no issues in the window, so average daily usage is zero and coverage is undefined. They are candidates for slow moving or obsolete review rather than zero days of supply.

#### Can I run this against a production ERP?

**Answer:** Use a reporting replica or run during off hours, and keep the account read-only. Large transaction tables benefit from an index on item and date.

## Search terms

days of supply sql query, inventory coverage sql, below safety stock query, sql inventory days on hand, erp inventory report sql, average daily usage sql, stock cover days query, weeks of supply sql

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