Spaces:
Running on Zero
Running on Zero
| -- Short-expiry risk analysis | |
| -- Compares stock on hand with recent sales velocity to estimate | |
| -- how much stock will NOT sell before it expires. | |
| -- Snapshot date = last date in the sales table. | |
| -- name: expiry_risk | |
| WITH snap AS ( | |
| SELECT MAX(date) AS d FROM sales | |
| ), | |
| velocity AS ( | |
| SELECT s.branch_id, s.product_id, SUM(s.qty_sold) / 28.0 AS avg_daily_units | |
| FROM sales s, snap | |
| WHERE s.date > date(snap.d, '-28 days') | |
| GROUP BY s.branch_id, s.product_id | |
| ), | |
| batches AS ( | |
| SELECT i.*, | |
| CAST(julianday(i.expiry_date) - julianday(snap.d) AS INTEGER) AS days_left | |
| FROM inventory_batches i, snap | |
| ) | |
| SELECT b.branch_id, | |
| br.branch_name, | |
| p.product_name, | |
| p.category, | |
| b.batch_id, | |
| b.qty_on_hand, | |
| b.expiry_date, | |
| b.days_left, | |
| ROUND(COALESCE(v.avg_daily_units, 0), 1) AS avg_daily_units, | |
| CAST(b.qty_on_hand - COALESCE(v.avg_daily_units, 0) * b.days_left AS INTEGER) | |
| AS projected_unsold, | |
| ROUND((b.qty_on_hand - COALESCE(v.avg_daily_units, 0) * b.days_left) | |
| * p.unit_cost, 2) AS value_at_risk | |
| FROM batches b | |
| JOIN products p ON p.product_id = b.product_id | |
| JOIN branches br ON br.branch_id = b.branch_id | |
| LEFT JOIN velocity v ON v.branch_id = b.branch_id AND v.product_id = b.product_id | |
| WHERE b.days_left <= 30 | |
| AND b.qty_on_hand - COALESCE(v.avg_daily_units, 0) * b.days_left >= 1 | |
| ORDER BY value_at_risk DESC; | |
| -- name: expiry_buckets | |
| WITH snap AS (SELECT MAX(date) AS d FROM sales) | |
| SELECT CASE | |
| WHEN julianday(i.expiry_date) - julianday(snap.d) <= 7 THEN '0-7 days' | |
| WHEN julianday(i.expiry_date) - julianday(snap.d) <= 14 THEN '8-14 days' | |
| WHEN julianday(i.expiry_date) - julianday(snap.d) <= 30 THEN '15-30 days' | |
| ELSE '30+ days' END AS bucket, | |
| COUNT(*) AS batches, | |
| SUM(i.qty_on_hand) AS units, | |
| ROUND(SUM(i.qty_on_hand * p.unit_cost), 2) AS stock_value | |
| FROM inventory_batches i | |
| JOIN products p ON p.product_id = i.product_id | |
| CROSS JOIN snap | |
| GROUP BY bucket | |
| ORDER BY MIN(julianday(i.expiry_date)); | |