Inventory Reconciliation — Solution & ExplanationProblems / Inventory Reconciliation / EditorialApproach

Fix the grain first. SUM(qty_change) GROUP BY product_id collapses the movement ledger to one net number per product. Joining the raw ledger instead would duplicate opening/closing values per movement row.

Anchor on the complete table. closing_stock covers every product, so it drives; opening_stock and the netted movements LEFT JOIN on.

COALESCE every absence to 0. Missing opening row = started at 0; no movements = nothing moved. Without both COALESCEs, NULL arithmetic silently blanks expected_closing — and a NULL comparison then *drops the row from the report*, hiding exactly the anomalies the report exists to find.

Filter to mismatches and show the signed discrepancy.

Key InsightReconciliation queries compare two independent accounts of the same truth (book stock vs counted stock), so their failure mode is asymmetric: a bug doesn't produce wrong numbers so much as *missing alarms*. The three-valued-logic trap is the sharpest version — closing <> NULL is unknown, unknown is filtered out, and the unbalanced product looks fine. Defensive COALESCE at every optional join leg is what keeps an audit query honest.Hints

Three tables, three grains: opening and closing are one row per product, movements are many. Net the movements per product FIRST, then every join is one-to-one.

Drive from closing_stock — it's the table guaranteed to cover every product, so LEFT JOINs from it can't lose anyone.

Two different absences both mean zero: a product missing from opening_stock (arrived mid-period) and a product with no movement rows. COALESCE each side independently — NULL + 40 is NULL, and that would hide a real discrepancy.

expected vs actual: keep rows where closing_qty <> expected_closing, and report the signed difference so shrinkage (negative) and overcounts (positive) are distinguishable.

Solve Inventory Reconciliation yourself →