# MRP Net Requirements SQL Query Generator

> Source: https://netray.co/tools/mrp-net-requirements-sql-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 and get a SQL query that computes projected available balance and the shortage per item and bucket.

## Category

developer

## Audience

- ERP developer
- Data analyst
- Production planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Demand table

**Key:** demand_table

**Type:** text

**Default:** mrp_demand

**Help:** One row per requirement

#### Supply table

**Key:** supply_table

**Type:** text

**Default:** mrp_supply

**Help:** Open POs and production orders

#### Stock table

**Key:** stock_table

**Type:** text

**Default:** item_stock

**Help:** One row per item with on hand and safety stock

#### Item column

**Key:** item_col

**Type:** text

**Default:** item_no

#### Bucket date column

**Key:** bucket_col

**Type:** text

**Default:** bucket_date

**Help:** Already truncated to day or week

#### Quantity column (demand and supply tables)

**Key:** qty_col

**Type:** text

**Default:** qty

#### On-hand column

**Key:** onhand_col

**Type:** text

**Default:** on_hand_qty

#### Safety stock column

**Key:** safety_col

**Type:** text

**Default:** safety_stock_qty

#### Horizon end date

**Key:** horizon

**Type:** date

**Default:** 2026-12-31

**Blocks:**

#### Projected available balance by item and bucket

**Body:** WITH keys AS (
  SELECT {{item_col}}, {{bucket_col}} FROM {{demand_table}}
  UNION
  SELECT {{item_col}}, {{bucket_col}} FROM {{supply_table}}
),
flow AS (
  SELECT k.{{item_col}},
         k.{{bucket_col}},
         COALESCE((SELECT SUM(d.{{qty_col}}) FROM {{demand_table}} d
                   WHERE d.{{item_col}} = k.{{item_col}}
                     AND d.{{bucket_col}} = k.{{bucket_col}}), 0) AS gross_req,
         COALESCE((SELECT SUM(s.{{qty_col}}) FROM {{supply_table}} s
                   WHERE s.{{item_col}} = k.{{item_col}}
                     AND s.{{bucket_col}} = k.{{bucket_col}}), 0) AS sched_receipts
  FROM keys k
  WHERE k.{{bucket_col}} <= DATE '{{horizon}}'
)
SELECT f.{{item_col}},
       f.{{bucket_col}},
       f.gross_req,
       f.sched_receipts,
       st.{{onhand_col}} + SUM(f.sched_receipts - f.gross_req)
         OVER (PARTITION BY f.{{item_col}} ORDER BY f.{{bucket_col}}
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS projected_available,
       st.{{safety_col}} AS safety_stock
FROM flow f
JOIN {{stock_table}} st ON st.{{item_col}} = f.{{item_col}}
ORDER BY f.{{item_col}}, f.{{bucket_col}};

#### Shortage list: first bucket below safety stock per item

**Body:** WITH proj AS (
  -- paste the query above here as a CTE, then filter
  SELECT * FROM projection_cte
)
SELECT {{item_col}},
       MIN({{bucket_col}}) AS first_short_bucket,
       MIN(projected_available - safety_stock) AS worst_gap
FROM proj
WHERE projected_available < safety_stock
GROUP BY {{item_col}}
ORDER BY first_short_bucket, {{item_col}};

**Notes:** Table and column names here are generic placeholders, not those of any specific ERP. Map them to your own demand, supply and stock views. The window syntax runs on PostgreSQL, SQL Server 2012 and later, Oracle and MySQL 8; the DATE literal may need CAST on SQL Server. The running balance does not create planned orders, so a negative balance shows the cumulative shortage, not a per-bucket order quantity. Test on a copy of the data first.

## Content

**Intro:** Planners and analysts often need a quick projected balance outside the ERP screen, for a supplier review or a shortage report. This generator writes the query from your own table and column names.

**Sections:**

#### How to use it

**Body:** Enter the names of your demand, supply and stock tables or views, the item and bucket date columns, and the horizon. The tool fills the placeholders and shows runnable SQL. Copy it into your SQL client, ideally against a reporting copy. Demand and supply must already be at one row per requirement or receipt with a bucket date, so truncate dates to day or week in a view first.

#### The logic behind the query

**Body:** Each bucket's net movement is receipts minus gross requirements. A running sum of that movement per item, ordered by bucket, added to starting on-hand stock, gives the projected available balance. That is exactly the balance row of the MRP record. Comparing it to safety stock shows where MRP would have to plan a new order.

#### Limits and common mistakes

**Body:** This is a netting projection, not a replacement for the MRP run.

**Bullets:**

- It ignores lot sizing, lead times and time fences.
- Demand from planned orders must be included if you want the dependent view.
- Mixing units of measure between tables gives wrong balances.
- Past-due receipts should be moved to the first bucket before running.
- Allocated stock should be removed from on hand if your ERP tracks it separately.

**Cta:** If you would rather ask for a shortage list in plain language and get the query shown back, ERPray does that read-only over live ERP data.

**Faq:**

#### Does this replace the MRP run?

**Answer:** No. It only nets existing demand and supply per bucket. It does not size planned orders, apply lead times, respect time fences or explode BOMs. Use it to check or explain what MRP shows, not to replace the engine.

#### Why use a window function for the balance?

**Answer:** A running sum per item ordered by date reproduces the projected available balance row in one pass, and it avoids slow self-joins. All major databases from recent versions support it.

#### How do I include past-due orders?

**Answer:** Create a view that sets the bucket date of any requirement or receipt earlier than today to the first bucket. Otherwise they appear in historic buckets and the running balance may start from the wrong point.

#### Can I run it on ERP production tables?

**Answer:** Prefer a reporting replica or read-only views. Large union and subquery plans can be heavy, so run off peak, add indexes on item and date columns, and have your DBA review it before scheduling.

## Search terms

mrp sql query, net requirements sql, projected available balance sql, mrp shortage report sql, sql running balance by item, inventory projection sql window function, mrp netting query, supply demand sql query

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