Converting local currencies into one target currency using the exchange rate table

Question

The solution is to be used on MS-SQL server. The presented code was tested on MS-SQL 2012.

I was given the exchange_rate table that reflects the human interface (for INSERT and UPDATE). Because of that, the exchange rate is expressed as two coefficient. Here is the table definition with the initial content:

IF (OBJECT_ID(N'dbo.exchange_rates', 'U') IS NULL) BEGIN

    CREATE TABLE dbo.exchange_rates (
        ID int IDENTITY PRIMARY KEY NOT NULL,
        date_from date NOT NULL,
        cur1 char(3) NOT NULL,     -- ISO 4217 (e.g. 'JPY')
        coef1 numeric(16, 9) NOT NULL,  -- coefficient for cur1 (e.g. 100)
        cur2 char(3) NOT NULL,          -- e.g. 'CZK'
        coef2 numeric(16, 9) NOT NULL   -- e.g. 21.9 then means 100 JPY = 21.9 CZK
    )

    -- Initial rows.
    INSERT INTO dbo.exchange_rates
        (date_from, coef1, cur1, coef2, cur2) VALUES
        ('2017-01-01', 27.020, 'CZK', 1, 'EUR'),
        ('2017-07-03', 1, 'EUR', 26.140, 'CZK'),
        ('2017-09-05', 1, 'EUR', 26.075, 'CZK'),
        ('2017-09-20', 1, 'EUR', 26.090, 'CZK'),

        ('2016-12-31', 100, 'JPY', 21.907, 'CZK'),
        ('2017-01-12', 1, 'EUR', 121.71586, 'JPY'),
        ('2017-07-01', 1, 'EUR', 128.42362, 'JPY')
END

Notice that some currency pairs may be switched (like in the first two sample rows).

Then I have whatever table that contains items with price expressed in the local currency. Here is the snippet to create a sample table that will be used by the later code:

IF (OBJECT_ID(N'dbo.order_details', 'U') IS NULL) BEGIN

    CREATE TABLE dbo.order_details (
        ID int IDENTITY PRIMARY KEY NOT NULL,
        date1 date,
        order_code varchar(10),
        part_no varchar(20),
        name nvarchar(100),
        price numeric(16, 4),
        cur char(3)
    )

    -- Initial rows.
    INSERT INTO dbo.order_details
        (date1, order_code, part_no, name, price, cur) VALUES
        ('2017-09-01', 'A2017001', 'PX105', 'product 1', 10.0000, 'EUR'),
        ('2017-09-01', 'A2017001', 'T128', 'product 2', 150.0000, 'JPY'),
        ('2017-09-01', 'A2017001', 'Z15', 'product 3', 25.5000, 'CZK'),
        ('2017-09-01', 'A2017001', 'int1', 'product 4', 125.0000, 'CZK'),
        ('2017-09-13', 'A2017002', 'zbo', 'product 5', 250.0000, 'CZK'),
        ('2017-09-13', 'A2017002', 'zbo2', 'product 6', 1.0000, 'EUR')
END

The full code that should solve the task is presented below. The @currency_to will become a stored procedure parameter in future -- here fixed to EUR. The code contain:

• Firstly, the destination currency is used to reduce the exchange_rates rows (here to EUR; say CZK/JPY pairs are not processed). Because of the possible cur1 and cur2 switch, the SELECT ... UNION ALL SELECT is used to unify the records into variable table @exchrates. The two coefficients are calculated to the rate value.

• Then the temporary table #exchrates is used to contains the enhanced rows where date_to column is added (for easier join with the real data later). Here the common table expression (cte) specific to MS-SQL is used for self-join to get the date_to from the next record. It is UNIONed with fictional dates that define the period before the first and after the last exchange rate row. Also the rate 1 (one) is used for the target currency.

• The last part shows the usage of the above constructed table.

---

Updated code (2017-10-10; see the original below):

The updated code uses the LEAD() function to avoid self join with common table expression (cte). On the other hand, I was not able to avoid cte when adding the row that covers the time period before the first explicit row with the rate.

DECLARE @currency_to char(3) = 'EUR'

-- Build the exchange-rate rows for the target currency in the variable.
-- The cur1 and cur2 can be switched in some rows; hence the UNION ALL.
DECLARE @exchrates AS TABLE (
    date_from date NOT NULL,
    cur1 char(3),
    cur2 char(3),
    rate float
)

INSERT INTO @exchrates (date_from, cur1, cur2, rate)
SELECT
    date_from,
    cur1,
    cur2,
    coef2 / coef1
FROM exchange_rates
WHERE cur2 = @currency_to

UNION ALL

SELECT
    date_from,
    cur2,
    cur1,
    coef1 / coef2
FROM exchange_rates
WHERE cur1 = @currency_to

--SELECT * FROM @exchrates

-- The temporary table will contain date intervals when the rate is valid.
CREATE TABLE #exchrates (
    date_from date NOT NULL,
    date_to date,
    cur1 char(3),   -- source currency
    cur2 char(3),   -- destination curr. (only for display; @currency_to is in each row)
    rate float
)


;WITH cte AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY cur1 ORDER BY date_from) AS rn
    FROM @exchrates
)
INSERT INTO #exchrates (date_from, date_to, cur1, cur2, rate)

-- Records to cover periods before the explicit exchange-rate record...
SELECT
    date_from = '1900-01-01',
    date_to = DATEADD(DAY, -1, date_from),
    cur1,
    cur2,
    rate
FROM cte
WHERE rn = 1

-- plus records derived from the explicit exchange-rate records...
UNION ALL
SELECT
    date_from,
    date_to = DATEADD(DAY, -1, COALESCE(LEAD(date_from) OVER (PARTITION BY cur1 ORDER BY date_from), '3000-01-01')),
    cur1,
    cur2,
    rate
FROM @exchrates AS k

-- plus one record for to convert from the target currency to the target currency with rate 1.
UNION ALL 
SELECT
    date_from = '1900-01-01',
    datum_do = '3000-01-01', 
    @currency_to, @currency_to, 1


SELECT * FROM #exchrates ORDER BY cur1, cur2, date_from


-- Here comes the test of the price recalculation for the products
-- from elsewhere to the currency defined by @currency_to.

SELECT
    d.date1,
    d.order_code,
    d.part_no,
    d.name,
    d.price,
    d.cur,
    r.*,
    d.price * r.rate AS price2
FROM order_details AS d
    LEFT OUTER JOIN #exchrates AS r
        ON d.cur = r.cur1           -- r.cur2 is always equal to @currency_to
            AND d.date1 BETWEEN r.date_from AND r.date_to

DROP TABLE #exchrates

The original code (see the updated code above):

DECLARE @currency_to char(3) = 'EUR'

-- Build the exchange-rate rows for the target currency in the variable.
-- The cur1 and cur2 can be switched in some rows; hence the UNION ALL.
DECLARE @exchrates AS TABLE (
    date_from date NOT NULL,
    cur1 char(3),
    cur2 char(3),
    rate float
)

INSERT INTO @exchrates (date_from, cur1, cur2, rate)
SELECT
    date_from,
    cur1,
    cur2,
    coef2 / coef1
FROM exchange_rates
WHERE cur2 = @currency_to

UNION ALL

SELECT
    date_from,
    cur2,
    cur1,
    coef1 / coef2
FROM exchange_rates
WHERE cur1 = @currency_to

--SELECT * FROM @exchrates

-- The temporary table will contain date intervals when the rate is valid.
CREATE TABLE #exchrates (
    date_from date NOT NULL,
    date_to date,
    cur1 char(3),   -- source currency
    cur2 char(3),   -- destination curr. (only for display; @currency_to is in each row)
    rate float
)

;WITH cte AS (
    SELECT date_from,
        cur1,
        cur2,       -- @currency_to is in each row
        rate,
        ROW_NUMBER() OVER (PARTITION BY cur1 ORDER BY date_from) AS rn,
        ROW_NUMBER() OVER (PARTITION BY cur1 ORDER BY date_from DESC) AS rn_desc
    FROM @exchrates
)
INSERT INTO #exchrates (date_from, date_to, cur1, cur2, rate)
SELECT
    t1.date_from,
    DATEADD(DAY, -1, t2.date_from),  -- one less day from the next record
    t1.cur1,
    t1.cur2,
    t1.rate
FROM cte AS t1, cte AS t2   -- self join
WHERE t1.cur1 = t2.cur1 AND t1.rn = t2.rn - 1 -- this and next row

UNION ALL

SELECT
    '1900-01-01',                   -- fictional date
    DATEADD(DAY, -1, t.date_from),  -- one less day than the date
    t.cur1,
    t.cur2,
    t.rate                          -- the best guess from the first real date
FROM cte AS t
WHERE t.rn = 1    -- period before the first record will have the same rate

UNION ALL

SELECT
    t.date_from,
    '3000-01-01',       -- fictional date to cover period after the last date
    t.cur1,
    t.cur2,
    t.rate
FROM cte AS t
WHERE t.rn_desc = 1    -- rate equal to the last known rate


-- Identity rate for the target currency.
INSERT INTO #exchrates (date_from, date_to, cur1, cur2, rate)
    VALUES ('1900-01-01', '3000-01-01', @currency_to, @currency_to, 1)

SELECT * FROM #exchrates ORDER BY cur1, cur2, date_from


-- Here comes the test of the price recalculation for the products
-- from elsewhere to the currency defined by @currency_to.

SELECT
    d.date1,
    d.order_code,
    d.part_no,
    d.name,
    d.price,
    d.cur,
    r.*,
    d.price * r.rate AS price2
FROM order_details AS d
    LEFT OUTER JOIN #exchrates AS r
        ON d.cur = r.cur1 AND r.cur2 = @currency_to
            AND d.date1 BETWEEN r.date_from AND r.date_to

DROP TABLE #exchrates

The results look like this...

First the #exchrates:

date_from   date_to     cur1 cur2 rate
1900-01-01  2016-12-31  CZK  EUR  0,0370096225018505
2017-01-01  2017-07-02  CZK  EUR  0,0370096225018505
2017-07-03  2017-09-04  CZK  EUR  0,0382555470543229
2017-09-05  2017-09-19  CZK  EUR  0,0383509108341323
2017-09-20  3000-01-01  CZK  EUR  0,0383288616328095
1900-01-01  3000-01-01  EUR  EUR  1
1900-01-01  2017-01-11  JPY  EUR  0,0082158561751936
2017-01-12  2017-06-30  JPY  EUR  0,0082158561751936
2017-07-01  3000-01-01  JPY  EUR  0,00778672957513579 

And the products with prices (the bar separates the original values from the calculated:

-------------------- from the order rows ---------------   ----------------- calculated from the exchange rates table -------------
date1       order_code  part_no name       price     cur | date_from   date_to     cur1 cur2  rate                price2
2017-09-01  A2017001    PX105   product 1  10.0000   EUR | 1900-01-01  3000-01-01  EUR  EUR   1                   10
2017-09-01  A2017001    T128    product 2  150.0000  JPY | 2017-07-01  3000-01-01  JPY  EUR   0,00778672957513579 1,16800943627037
2017-09-01  A2017001    Z15     product 3  25.5000   CZK | 2017-07-03  2017-09-04  CZK  EUR   0,0382555470543229  0,975516449885233
2017-09-01  A2017001    int1    product 4  125.0000  CZK | 2017-07-03  2017-09-04  CZK  EUR   0,0382555470543229  4,78194338179036
2017-09-13  A2017002    zbo     product 5  250.0000  CZK | 2017-09-05  2017-09-19  CZK  EUR   0,0383509108341323  9,58772770853308
2017-09-13  A2017002    zbo2    product 6  1.0000    EUR | 1900-01-01  3000-01-01  EUR  EUR   1                   1    

I would like to find a possible design flaws (a better solution). Thanks.

The question is related to https://stackoverflow.com/q/46585724/1346705

Answer 1

Some remarks:

• It is better to have one currency table based on the ISO 4217 standard for currency codes and IDs. Make the primary key the ISO currency ID (an integer).

  When you refer to a currency in other tables (e.g. an exchange rate table), store the currency ID rather than the currency code. Index lookup is faster on integers than on CHAR columns. A comparison based on character fields needs to compare each character in the CHAR(3) column, while comparing an INT column needs only one comparison.

  Make sure to make a foreign key from those tables to the currency table, consistency being important and all that.

• When it comes to storing information temporarily, usually you should avoid using table variables and instead choose to store that information in a temporary table. This very detailed answer by Martin Smith gives an explanation on the difference between those two: What's the difference between a temp table and table variable in SQL Server?.

  What's relevant in your case, is that the cardinality estimation for queries using the table variable are likely wrong. You can see this from the estimated and actual execution plan that cardinality estimates are all 1. This may result in a suboptimal execution plan.

  Note also, since you're doing a ROW_NUMBER() OVER (PARTITION BY cur1 ORDER BY date_from) (partitioning on @exchrates.cur1 column and ordering on @exchrates.date_from) you will likely benefit from an index on @exchrates.cur1 + @exchrates.date_from. This must be a UNIQUE INDEX/PRIMARY KEY for table variables in versions before SQL Server 2014; SQL Server 2014 onwards allows for non unique indexes.

  In any case, as I assume you have a lot of rows in your exchange rate table, you will likely benefit from populating intermediate results in a temporary table with a suitable clustered index.

• The #exchrates is missing a clustered index. A clustered index would benefit lookup in your final query linking the orders to exchange rates. A good candidate would be cur + date_from. Make sure you create this index before inserting any data in that table.

• The first intermediate step, populating results in @exchrates is superfluous; you can simply have the query that populates the table variable as a derived table in the query that populates #exchrates.

• Beware of parameter sniffing when making this a stored procedure. The execution plan for 'EUR' might be inefficient for 'CSZ' if the cardinalities differ wildly.