SQL Days of Supply and Inventory Coverage Query Generator

-- Generic SQL (ANSI style). Replace table and column names with your own.
-- Date arithmetic differs by database: adjust the window filter below.
WITH usage AS (
  SELECT item_no, SUM(qty) AS total_issued,
         SUM(qty) / 90.0 AS avg_daily_usage
  FROM inventory_transactions
  WHERE txn_type = 'ISSUE'
    AND txn_date >= CURRENT_DATE - INTERVAL '90' DAY
  GROUP BY item_no
)
SELECT s.item_no,
       s.site_id,
       s.qty_on_hand AS on_hand,
       s.safety_stock_qty AS safety_stock,
       COALESCE(u.avg_daily_usage, 0) AS avg_daily_usage,
       CASE WHEN COALESCE(u.avg_daily_usage, 0) = 0 THEN NULL
            ELSE s.qty_on_hand / u.avg_daily_usage END AS days_of_supply,
       CASE WHEN s.qty_on_hand < s.safety_stock_qty THEN 'BELOW SAFETY' ELSE 'OK' END AS status
FROM item_stock s
LEFT JOIN usage u ON u.item_no = s.item_no
WHERE s.site_id = 'PLANT01'
ORDER BY days_of_supply ASC;

SELECT s.item_no, s.site_id, s.qty_on_hand, s.safety_stock_qty,
       s.safety_stock_qty - s.qty_on_hand AS shortfall
FROM item_stock s
WHERE s.site_id = 'PLANT01'
  AND s.safety_stock_qty > 0
  AND s.qty_on_hand < s.safety_stock_qty
ORDER BY shortfall DESC;

SELECT s.item_no, s.qty_on_hand,
       s.qty_on_hand / (u.total_issued / 90.0) AS days_of_supply
FROM item_stock s
JOIN (SELECT item_no, SUM(qty) AS total_issued
      FROM inventory_transactions
      WHERE txn_type = 'ISSUE'
        AND txn_date >= CURRENT_DATE - INTERVAL '90' DAY
      GROUP BY item_no) u
  ON u.item_no = s.item_no
WHERE u.total_issued > 0
  AND s.qty_on_hand / (u.total_issued / 90.0) > 365;