Why does today’s dispatch report use next month’s address?

Prepare the diagnostic input

A customer can pre-register a future address. The latest stored address is not necessarily the address effective on 10 October. Filter valid_from before ranking, keeping only dates at or before the fixed dispatch report date.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE addresses(id INTEGER PRIMARY KEY,customer TEXT,valid_from TEXT,address TEXT);
INSERT INTO addresses VALUES (1,'C1','2026-01-01','Demo Unit A'),(2,'C1','2026-11-01','Demo Unit B');

Reproduce the misleading result and correct it

WITH ranked AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY customer ORDER BY valid_from DESC,id DESC) AS rn FROM addresses)
SELECT customer,address FROM ranked WHERE rn=1 ORDER BY customer;

WITH ranked AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY customer ORDER BY valid_from DESC,id DESC) AS rn
FROM addresses WHERE valid_from<='2026-10-10')
SELECT customer,address FROM ranked WHERE rn=1 ORDER BY customer;

The naive result chooses Demo Unit B from 1 November. The corrected result is Demo Unit A, effective since January. This creates a review report only; it does not authorise shipment or prove the customer’s address is deliverable.

Check the boundary before applying the fix

Add Demo Unit C effective exactly 10 October. The start-inclusive policy makes C applicable that day. The higher revision ID resolves only same-date ordering under the stated fixture rule.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO addresses VALUES (3,'C1','2026-10-10','Demo Unit C');

There is no valid-to date, overlapping address category or timezone here. A customer with no eligible address needs an explicit hold, not a future-address fallback.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.