# Plant Monthly Business Review SQL Generator

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

## Short answer

Fill in schema and table names and get four SQL queries for monthly review KPIs: on-time delivery, scrap rate, aged WIP and completed units by week.

## Category

developer

## Audience

- Plant manager
- ERP analyst
- COO

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Schema

**Key:** schema

**Type:** text

**Default:** mfg

#### Production order table

**Key:** orders

**Type:** text

**Default:** production_orders

#### Shipment line table

**Key:** shipments

**Type:** text

**Default:** shipment_lines

#### Plant code

**Key:** plant

**Type:** text

**Default:** PLANT01

#### Period start

**Key:** start

**Type:** date

**Default:** 2026-09-01

#### Period end (exclusive)

**Key:** end

**Type:** date

**Default:** 2026-10-01

#### WIP aged after (days)

**Key:** aged_days

**Type:** text

**Default:** 30

**Blocks:**

#### 1. On-time delivery (shipment lines)

**Body:** -- Assumes columns: plant, promise_date, ship_date, ship_qty
SELECT
  COUNT(*) AS lines_shipped,
  SUM(CASE WHEN ship_date <= promise_date THEN 1 ELSE 0 END) AS lines_on_time,
  ROUND(100.0 * SUM(CASE WHEN ship_date <= promise_date THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS otd_pct
FROM {{schema}}.{{shipments}}
WHERE plant = '{{plant}}'
  AND ship_date >= '{{start}}'
  AND ship_date < '{{end}}';

#### 2. Scrap rate by item

**Body:** -- Assumes columns: plant, item, qty_completed, qty_scrapped, complete_date
SELECT
  item,
  SUM(qty_completed) AS good_qty,
  SUM(qty_scrapped) AS scrap_qty,
  ROUND(100.0 * SUM(qty_scrapped) / NULLIF(SUM(qty_completed) + SUM(qty_scrapped), 0), 2) AS scrap_pct
FROM {{schema}}.{{orders}}
WHERE plant = '{{plant}}'
  AND complete_date >= '{{start}}'
  AND complete_date < '{{end}}'
GROUP BY item
HAVING SUM(qty_scrapped) > 0
ORDER BY scrap_qty DESC;

#### 3. Open orders past due and WIP age

**Body:** -- Assumes columns: order_no, item, status, release_date, due_date, qty_ordered, qty_completed
SELECT
  order_no,
  item,
  due_date,
  qty_ordered - qty_completed AS qty_open,
  DATEDIFF(day, release_date, '{{end}}') AS age_days
FROM {{schema}}.{{orders}}
WHERE plant = '{{plant}}'
  AND status = 'RELEASED'
  AND DATEDIFF(day, release_date, '{{end}}') > {{aged_days}}
ORDER BY age_days DESC;

#### 4. Completed units by week

**Body:** -- DATEPART(week, ...) is SQL Server syntax. Use date_trunc('week', complete_date) in PostgreSQL.
SELECT
  DATEPART(week, complete_date) AS week_no,
  SUM(qty_completed) AS units_completed,
  COUNT(DISTINCT order_no) AS orders_closed
FROM {{schema}}.{{orders}}
WHERE plant = '{{plant}}'
  AND complete_date >= '{{start}}'
  AND complete_date < '{{end}}'
GROUP BY DATEPART(week, complete_date)
ORDER BY week_no;

**Notes:** Table and column names are generic placeholders, not those of any specific ERP. Map them to your system (for example job and shipment tables) and check dialect functions such as DATEDIFF and DATEPART for your database. Run read-only against a reporting replica.

## Content

**Intro:** A monthly operations review needs the same four or five numbers, pulled the same way each time. This generator produces plain SQL for on-time delivery, scrap by item, aged work in process and weekly completions, ready to adapt to your ERP tables.

**Sections:**

#### How to use it

**Body:** Enter your schema, the production order and shipment table names, plant code and period. Copy each query into your database tool, then rename columns to match your ERP. Start with one query, check the result against a report you trust, and only then schedule it. Use the period end as an exclusive bound so the next month starts cleanly with no overlapping day.

**Bullets:**

- Run against a read-only reporting replica where possible
- Compare the first result with your existing KPI report
- Keep period bounds as parameters, never hard-coded dates

#### The definitions behind the queries

**Body:** On-time delivery here is share of shipment lines shipped on or before the promise date. Some customers measure against the request date or use a delivery window, so agree the definition before you compare plants. Scrap rate is scrap divided by good plus scrap quantity. Aged WIP uses days since release and a threshold you set. These are common definitions, not a standard.

#### Reading the output

**Body:** Look at OTD as a trend, not one month. Scrap by item should be read next to quantity, because a high percentage on a tiny run matters less than a moderate percentage on a high-volume part. Aged WIP often reveals orders waiting for material or inspection. The weekly completions give a rhythm check: a big spike in the last week is a sign of end-of-month pushing.

#### Common mistakes

**Body:** Frequent errors include counting orders instead of lines for OTD, mixing time zones in date filters, including reworked quantity twice, and comparing numbers after a change of definition. Always document the definition next to the number in the review pack.

**Cta:** If you would rather ask these questions in plain language against live ERP data, with the SQL shown and read-only access, Netray's ERPray does that. The queries above work on their own.

**Faq:**

#### Will these queries run on my ERP unchanged?

**Answer:** No. The table and column names are generic. You need to map them to your ERP's actual tables and check date functions for your database. The structure and logic carry over.

#### Why exclusive end dates?

**Answer:** A less-than comparison with the first day of the next period avoids missing records timestamped late on the last day and avoids double counting when the next report starts.

#### Which database dialect is used?

**Answer:** Mostly ANSI SQL, with DATEDIFF and DATEPART in SQL Server style. The notes show the PostgreSQL alternative. Oracle users would use date arithmetic and TRUNC.

#### Can I use this for write operations?

**Answer:** These are SELECT queries only. Never run reporting queries with accounts that can modify production data.

## Search terms

manufacturing kpi sql query, on time delivery sql, scrap rate sql query, wip aging sql, monthly operations review sql, production orders sql template, plant kpi query generator

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