Spaces:
Running on Zero
Running on Zero
File size: 2,208 Bytes
f19868d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | -- 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));
|