MRP Net Requirements SQL Query Generator

WITH keys AS (
  SELECT item_no, bucket_date FROM mrp_demand
  UNION
  SELECT item_no, bucket_date FROM mrp_supply
),
flow AS (
  SELECT k.item_no,
         k.bucket_date,
         COALESCE((SELECT SUM(d.qty) FROM mrp_demand d
                   WHERE d.item_no = k.item_no
                     AND d.bucket_date = k.bucket_date), 0) AS gross_req,
         COALESCE((SELECT SUM(s.qty) FROM mrp_supply s
                   WHERE s.item_no = k.item_no
                     AND s.bucket_date = k.bucket_date), 0) AS sched_receipts
  FROM keys k
  WHERE k.bucket_date <= DATE '2026-12-31'
)
SELECT f.item_no,
       f.bucket_date,
       f.gross_req,
       f.sched_receipts,
       st.on_hand_qty + SUM(f.sched_receipts - f.gross_req)
         OVER (PARTITION BY f.item_no ORDER BY f.bucket_date
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS projected_available,
       st.safety_stock_qty AS safety_stock
FROM flow f
JOIN item_stock st ON st.item_no = f.item_no
ORDER BY f.item_no, f.bucket_date;

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