How much physical stock is still available to promise?

Define the decision and input records

A reservation changes availability without moving a physical item. Sum only confirmed reservations once per SKU, then subtract from counted stock. The left join keeps items with no reservation visible; COALESCE treats that missing total as zero rather than removing the item.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE stock(sku TEXT PRIMARY KEY, opening_qty INTEGER, counted_qty INTEGER);
CREATE TABLE movements(id INTEGER PRIMARY KEY, sku TEXT, qty INTEGER, moved_on TEXT);
CREATE TABLE reservations(id INTEGER PRIMARY KEY, sku TEXT, qty INTEGER, status TEXT);
CREATE TABLE transfers(id INTEGER PRIMARY KEY, sku TEXT, sent_qty INTEGER, received_qty INTEGER, sent_on TEXT);
INSERT INTO stock VALUES ('A',10,12),('B',8,3),('C',6,6);
INSERT INTO movements VALUES (1,'A',5,'2026-10-01'),(2,'A',-2,'2026-10-02'),(3,'B',-5,'2026-10-03'),(4,'C',0,'2026-08-01');
INSERT INTO reservations VALUES (1,'A',8,'confirmed'),(2,'A',3,'cancelled'),(3,'B',5,'confirmed');
INSERT INTO transfers VALUES (1,'A',7,4,'2026-10-05'),(2,'B',2,2,'2026-10-06');

Run the query and compare the result

WITH held AS (
 SELECT sku,SUM(qty) AS reserved_qty FROM reservations
 WHERE status='confirmed' GROUP BY sku
)
SELECT s.sku,s.counted_qty,COALESCE(h.reserved_qty,0) AS reserved_qty,
       s.counted_qty-COALESCE(h.reserved_qty,0) AS available_qty
FROM stock s LEFT JOIN held h ON h.sku=s.sku ORDER BY s.sku;

A has 4 available units. B has −2: five units are promised against three counted units. C has all six units available. The negative balance calls for an allocation decision or a count review; changing it to zero would conceal the shortage. A cancelled reservation does not consume stock.

Apply the result within its limits

No incoming supply, safety stock, batch expiry or warehouse transfer is included. Do not present this simple available quantity as a full capable-to-promise calculation.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.