Inventory Management

Business Logic

Available stock: quantity_on_hand from inventory where quantity_on_hand > 0

Products needing reorder: total quantity_on_hand across warehouses <= product's reorder_point

Active products only: Exclude discontinued = true unless analyzing discontinued items

Example Query

-- Find products below reorder point
SELECT p.product_id, p.product_name, p.reorder_point, SUM(i.quantity_on_hand) as total_stock
FROM products p
JOIN inventory i ON p.product_id = i.product_id
WHERE p.discontinued = false
GROUP BY p.product_id, p.product_name, p.reorder_point
HAVING SUM(i.quantity_on_hand) <= p.reorder_point;