Why does spare stock in another warehouse hide a local shortage?

Prepare the diagnostic input

The team needs a replenishment list by warehouse. Summing quantities and minima across warehouses can let a surplus offset a local shortfall. Join SKU and warehouse together, then apply the rule at that grain. A possible transfer is a later operational decision.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE levels(sku TEXT,warehouse TEXT,qty INTEGER,PRIMARY KEY(sku,warehouse));
CREATE TABLE reorder_rules(sku TEXT,warehouse TEXT,min_qty INTEGER,PRIMARY KEY(sku,warehouse));
INSERT INTO levels VALUES ('A','W1',3),('A','W2',10);
INSERT INTO reorder_rules VALUES ('A','W1',5),('A','W2',2);

Reproduce the misleading result and correct it

SELECT l.sku,SUM(l.qty) AS qty,SUM(r.min_qty) AS min_qty FROM levels l
JOIN reorder_rules r ON r.sku=l.sku AND r.warehouse=l.warehouse
GROUP BY l.sku HAVING SUM(l.qty)<SUM(r.min_qty) ORDER BY l.sku;

SELECT l.sku,l.warehouse,l.qty,r.warehouse AS rule_warehouse,r.min_qty FROM levels l
JOIN reorder_rules r ON r.sku=l.sku AND r.warehouse=l.warehouse WHERE l.qty<r.min_qty ORDER BY l.sku,l.warehouse;

W1 has three units against a five-unit threshold and needs review. W2 has ten against two. The combined 13 against seven looks healthy and hides W1. The correction lists W1 without assuming W2’s units are transferable or immediately available there.

Check the boundary before applying the fix

Raise A/W2’s own minimum to twelve, then add B/W1 at three units against five. The report must retain SKU as well as warehouse: A/W1 and B/W1 have equal quantities and thresholds but are different replenishment tasks.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

UPDATE reorder_rules SET min_qty=12 WHERE warehouse='W2';
INSERT INTO levels VALUES ('B','W1',3);
INSERT INTO reorder_rules VALUES ('B','W1',5);

Thresholds are supplied policy values, not demand forecasts. Reservations, lead times, batch restrictions and transfer cost are excluded.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.