Which stock counts disagree with the movement ledger?

Define the decision and input records

Use one opening balance per SKU and positive receipts or negative issues in the same unit. Aggregate movements before comparing with the count. A difference is a review flag: adjusting the count immediately would hide whether the problem is a missing movement, a counting error or an incorrect opening balance.

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

SELECT s.sku, s.opening_qty + COALESCE(SUM(m.qty),0) AS ledger_qty,
       s.counted_qty, s.counted_qty - (s.opening_qty + COALESCE(SUM(m.qty),0)) AS difference
FROM stock s LEFT JOIN movements m ON m.sku=s.sku
GROUP BY s.sku,s.opening_qty,s.counted_qty
HAVING difference <> 0 ORDER BY s.sku;

A has a ledger balance of 13 and a physical count of 12, so its difference is −1. B agrees at 3; C agrees at 6. Compare A’s count sheet and movement references before posting an authorised correction. An agreement does not prove every movement is valid because offsetting errors can cancel out.

Apply the result within its limits

This fixture has one location and one count per SKU. Multiple warehouses need a SKU-and-location key and a count cut-off; movements posted after that cut-off must not enter the reconciliation.

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.