PartsBelowReorderPoint: usage rate, not a fixed number, sets the threshold
-- PartsBelowReorderPoint: flag any part whose stock on hand won't last
-- through its own supplier lead time, based on ACTUAL recent usage rather
-- than a fixed reorder quantity that goes stale as demand shifts.
WITH DailyUsage AS (
-- One row per part per day it moved at all. Only negative movements
-- count as usage, so a delivery received doesn't cancel out real demand.
SELECT
part_id,
movement_date,
-SUM(quantity_change) AS units_used
FROM StockMovements
WHERE quantity_change < 0
AND movement_date >= DATE('now', '-28 days')
GROUP BY part_id, movement_date
),
AverageUsage AS (
-- Average daily usage per part across the trailing 28-day window.
-- AVG() OVER a partition keeps the per-part average and the per-day
-- detail in the same result set, rather than needing a second
-- GROUP BY pass purely to collapse DailyUsage down to one row per part.
SELECT
part_id,
AVG(units_used) OVER (PARTITION BY part_id) AS avg_daily_usage
FROM DailyUsage
)
SELECT DISTINCT
p.part_id,
p.part_name,
p.quantity_on_hand,
p.lead_time_days,
ROUND(au.avg_daily_usage, 2) AS avg_daily_usage,
ROUND(au.avg_daily_usage * p.lead_time_days, 0) AS reorder_point
FROM Parts AS p
INNER JOIN AverageUsage AS au ON au.part_id = p.part_id
WHERE p.quantity_on_hand <= au.avg_daily_usage * p.lead_time_days
ORDER BY (p.quantity_on_hand - au.avg_daily_usage * p.lead_time_days) ASC;
The reorder point itself is one idea: average daily usage multiplied by lead time days, which is exactly the stock needed to bridge the gap between placing an order and it arriving. A part currently at or below that figure is at real risk of running out before a fresh order can land.
Ordering by how far below the threshold each part sits, rather than alphabetically or by part number, puts the most urgent shortages at the top of the report without anyone having to scan the whole list to find them.
For speed: an index on StockMovements(part_id, movement_date) matters here more than most queries on this site, since StockMovements grows with every single sale and repair, and this report is meant to run daily, not as a one-off.