Why does today’s rate change an earlier purchase amount?

Prepare the diagnostic input

The fixture’s policy uses the most recent supplied rate at or before purchase date, with HKD per USD as the direction. A global latest-rate subquery revalues all purchases by one later rate. Correlate rate selection to each purchase and leave a missing historical rate NULL.

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 purchases(id TEXT PRIMARY KEY,bought_on TEXT,usd_cents INTEGER);
CREATE TABLE rates(valid_from TEXT PRIMARY KEY,hkd_per_usd REAL);
INSERT INTO purchases VALUES ('P1','2026-10-02',10000),('P2','2026-10-06',10000);
INSERT INTO rates VALUES ('2026-10-01',7.8),('2026-10-05',7.7);

Reproduce the misleading result and correct it

SELECT p.id,CAST(ROUND(p.usd_cents*r.hkd_per_usd,0) AS INTEGER) AS hkd_cents
FROM purchases p CROSS JOIN rates r WHERE r.valid_from=(SELECT MAX(valid_from) FROM rates) ORDER BY p.id;

SELECT p.id,CAST(ROUND(p.usd_cents*r.hkd_per_usd,0) AS INTEGER) AS hkd_cents
FROM purchases p LEFT JOIN rates r ON r.valid_from=(SELECT MAX(valid_from) FROM rates WHERE valid_from<=p.bought_on) ORDER BY p.id;

P1 uses the invented 7.8 rate, giving 78000 HKD cents; P2 uses 7.7, giving 77000. Both begin as 10000 USD cents. These are supplied test rates, not current rates or a statement of an accounting-standard conversion policy.

Check the boundary before applying the fix

P0 predates the first rate. Keep P0 visible with NULL converted cents. Omitting it or borrowing a future rate would hide the unresolved conversion.

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 purchases VALUES ('P0','2026-09-30',10000);

Only one currency pair and an agreed rounding step are modelled. Missing rates, inverse rates and multiple approved rate types need separate policies.

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.