Currency Rates by Effective Date: Designing the Lookup Table

Currency rates need three things: a currency pair, an effective date, and a clear quote direction. With those, you can find the latest rate on or before each order date. You also keep the orders that have no rate visible, instead of letting them quietly vanish from a total.

Why a single rate column is not enough

A junior DBA once asked me why last quarter’s invoice totals changed overnight. The finance team had updated “the rate.” There was only one rate per currency in the table, so every old invoice was suddenly converted with today’s number. Nobody lied. The design just had no memory.

A rate is a fact about a day. So the table needs the day. It also needs to say which way the rate points. Our demo stores target units per one source unit. USD to EUR at 0.91 means one US dollar buys 0.91 euros. Flip the pair without changing the number, and the rate now means something false.

Build the table and the orders

The primary key is the pair plus the effective date. That allows one rate per pair per day. An intraday feed would need a timestamp or a rule for picking one quote. The values here are made-up test data, not real quotations.

DROP TABLE IF EXISTS #CurrencyRate, #Orders;

CREATE TABLE #CurrencyRate (
    FromCurrency char(3) NOT NULL,
    ToCurrency   char(3) NOT NULL,
    EffectiveDate date   NOT NULL,
    Rate decimal(19,8)   NOT NULL CHECK (Rate > 0),
    PRIMARY KEY (FromCurrency, ToCurrency, EffectiveDate)
);
CREATE TABLE #Orders (
    OrderId int PRIMARY KEY, OrderDate date,
    FromCurrency char(3), ToCurrency char(3), Amount decimal(19,4)
);
INSERT #CurrencyRate VALUES
    ('USD', 'EUR', '20260924', 0.90000000),
    ('USD', 'EUR', '20260925', 0.91000000);
INSERT #Orders VALUES
    (1, '20260926', 'USD', 'EUR', 100),
    (2, '20260920', 'USD', 'EUR', 100),
    (3, '20260924', 'USD', 'EUR', 250.50),
    (4, '20260926', 'EUR', 'USD', 100);

Try to load a second rate for the same pair and day. The key says no.

BEGIN TRY
    INSERT #CurrencyRate VALUES ('USD', 'EUR', '20260925', 0.95000000);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, 'Second rate for the same day rejected' AS what_happened;
END CATCH;

You get error 2627, a primary key violation. Good. The table will not guess which of two daily rates you meant.

Look backward from each order date

Now the lookup. OUTER APPLY with TOP (1) picks the latest rate on or before the order date for the exact pair. OUTER keeps an order even when no rate qualifies. The unique key makes the choice unambiguous.

DROP TABLE IF EXISTS #Converted;

SELECT o.OrderId, o.OrderDate, r.EffectiveDate, r.Rate,
       CAST(o.Amount * r.Rate AS decimal(19,4)) AS converted_amount,
       DATEDIFF(day, r.EffectiveDate, o.OrderDate) AS rate_age_days,
       CASE WHEN r.Rate IS NULL THEN 'Missing rate' ELSE 'Rate found' END AS rate_status
INTO #Converted
FROM #Orders AS o
OUTER APPLY (
    SELECT TOP (1) cr.EffectiveDate, cr.Rate
    FROM #CurrencyRate AS cr
    WHERE cr.FromCurrency = o.FromCurrency
      AND cr.ToCurrency = o.ToCurrency
      AND cr.EffectiveDate <= o.OrderDate
    ORDER BY cr.EffectiveDate DESC
) AS r;

SELECT * FROM #Converted ORDER BY OrderId;

Order 1 uses the September 25 rate and turns 100 into 91.0000. The rate is one day old. Order 3 falls exactly on September 24, so it uses that day’s 0.90 and gives 225.4500. Order 2 is older than the earliest rate, so it stays in the result with NULLs and the status Missing rate.

Order 4 is the reversed pair, EUR to USD. We only loaded USD to EUR, so it is Missing rate too. That is the right answer. Inverting a rate is a business decision, and it needs its own rule about rounding. Do not let a query make it for you.

Keep the gaps visible

A latest-known lookup carries an old rate forward. That is a policy, not a law of accounting. The rate_age_days column shows how stale each rate is, so you can set a limit.

Missing rows are the dangerous ones. A SUM ignores NULLs, so the total looks complete while two orders are not in it. Always reconcile counts next to the total.

SELECT COUNT(*) AS orders_total,
       COUNT(Rate) AS orders_converted,
       COUNT(*) - COUNT(Rate) AS orders_missing_rate,
       SUM(converted_amount) AS converted_total
FROM #Converted;

Four orders, two converted, two missing, and a total of 316.4500. If you only read the total, you would never know half the orders were left out.

Decide the rounding on purpose

The final CAST chooses how many decimal places you keep. Look at 0.55 converted at 0.91. With four places you get 0.5005. With two places you get 0.50. Over a million rows, that difference is real money. Pick the scale your finance team signs off on, and test small and negative amounts too.

DECLARE @Rate decimal(19,8) = 0.91, @Amount decimal(19,4) = 0.55;

SELECT CAST(@Amount * @Rate AS decimal(19,4)) AS four_places,
       CAST(@Amount * @Rate AS decimal(19,2)) AS two_places;

DROP TABLE IF EXISTS #Converted, #Orders, #CurrencyRate;

One more habit. If an accepted invoice must never change, store the rate you applied on the invoice row itself. A later correction to the rate table should not rewrite history.

Load a few days of your own rates and look for the orders that come back with a Missing rate.

A historical conversion is not current arithmetic, it is arithmetic under an effective-rate rule.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.