Plant Monthly Business Review SQL Generator

-- 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 mfg.shipment_lines
WHERE plant = 'PLANT01'
  AND ship_date >= '2026-09-01'
  AND ship_date < '2026-10-01';

-- 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 mfg.production_orders
WHERE plant = 'PLANT01'
  AND complete_date >= '2026-09-01'
  AND complete_date < '2026-10-01'
GROUP BY item
HAVING SUM(qty_scrapped) > 0
ORDER BY scrap_qty DESC;

-- 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, '2026-10-01') AS age_days
FROM mfg.production_orders
WHERE plant = 'PLANT01'
  AND status = 'RELEASED'
  AND DATEDIFF(day, release_date, '2026-10-01') > 30
ORDER BY age_days DESC;

-- 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 mfg.production_orders
WHERE plant = 'PLANT01'
  AND complete_date >= '2026-09-01'
  AND complete_date < '2026-10-01'
GROUP BY DATEPART(week, complete_date)
ORDER BY week_no;