Define the decision and input records
A movement date must represent an actual nonzero change. MAX over only nonzero rows finds the latest meaningful movement. Keep NULL as an unknown history rather than converting it to an old date: an item with no movement history is different from an item known to have last moved months ago.
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.
| id | sku | qty | moved_on |
|---|---|---|---|
| 1 | A | 5 | 2026-10-01 |
| 2 | A | -2 | 2026-10-02 |
| 3 | B | -5 | 2026-10-03 |
| 4 | C | 0 | 2026-08-01 |
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.counted_qty,MAX(CASE WHEN m.qty<>0 THEN m.moved_on END) AS last_movement
FROM stock s LEFT JOIN movements m ON m.sku=s.sku
GROUP BY s.sku,s.counted_qty
HAVING s.counted_qty>0 AND (last_movement IS NULL OR last_movement<'2026-09-10')
ORDER BY s.sku;| sku | counted_qty | last_movement |
|---|---|---|
| C | 6 | NULL (unknown or absent) |
C has six counted units but no nonzero movement in the supplied ledger. Its zero-quantity row does not prove a receipt or sale occurred. A and B have October movements and are excluded by the 10 September cut-off. Ask whether C’s history is incomplete before labelling it slow-moving.
Apply the result within its limits
The 30-calendar-day interval is an example policy, not an inventory valuation rule. This report cannot determine shelf life, obsolete value or true stock age without batch receipts and a complete ledger.
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.