Which warehouse transfers are still unreceived?

Define the decision and input records

Treat dispatch and receipt as separate quantities for the same transfer. Received quantity may be partial, so a yes/no received flag loses useful information. The example uses a fixed report date of 10 October 2026, allowing the same elapsed-day result to be reproduced later.

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 id,sku,sent_qty,received_qty,sent_qty-received_qty AS in_transit_qty,
 CAST(julianday('2026-10-10')-julianday(sent_on) AS INTEGER) AS days_since_sent
FROM transfers WHERE sent_qty>received_qty ORDER BY id;

Transfer 1 sent seven A units and received four, leaving three in transit after five calendar days. Transfer 2 is complete and is omitted. Check receipt evidence and the destination warehouse before declaring those three units lost or adding them to available stock.

Apply the result within its limits

The example stores cumulative received quantity. An event-based receiving system should aggregate confirmed receipt lines first and separately flag receipts exceeding dispatch. Elapsed days are not a delivery SLA or working days.

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.