How to efficiently convert the price in SELECT using the existing exchange rate table with given structure?

Question

Situation: I have the exchange rate table like this:

date_from   cur1 coef1          cur2 coef2
2017-01-01  CZK  27.000000000   EUR  1.000000000
2017-07-03  EUR  1.000000000    CZK  26.150000000
2017-07-03  JPY  100.000000000  CZK  19.500000000
2017-10-05  JPY  1000.0000000   EUR  7.54761885

Notice that sometimes the cur1 and cur2 can be switched for the same pair. The table contains also other currency pairs. The reason for the two coefficients is that the table is filled manually (to get the numbers more comprehensible by a human brain -- see the JPY conversion).

Then I have another table with invoice rows where the price is expressed in the local currency (that is, each row has it own currency unit near the price value).

I need to do some SELECT over the invoice-row table and transform the price to be shown in the chosen target currency (say, everything in Euro). How to do that efficiently?

My first attempts: I know the target currency in advance. It means it should probably be better to build a temporary table with simplified structure to be joined easily. Let the target currency be EUR. Then only subset of the above table will be used, some pairs be switched, and the two coefficients be converted to one rate. The target currency will be fixed or implicit. From the above table, the JPY-CZK pair would not be part of the table:

date_from   cur  rate
2017-01-01  CZK  27.000000000
2017-07-03  CZK  26.150000000
2017-10-05  JPY  0.00754761885

To join the rows with another table I need not only the date_from but also date_to. To be able to use BETWEEN in the join condition, I would like to have the date_to as the one just before the next period. Here for CZK, I need to have a record like:

date_from   date_to     cur  rate
2017-01-01  2017-07-02  CZK  27.000000000

Notice the one day off in the date_to from the next date_from.

However, I need to add automatically also some boundary values for the dates before and after the explicitly expressed intervals. I need something like that:

date_from   date_to     cur  rate
1900-01-01  2016-12-31  CZK  27.000000000   <-- guessed rate from the next; fixed start at the year 1900
2017-01-01  2017-07-02  CZK  27.000000000
2017-07-03  3000-01-01  CZK  26.150000000   <-- explicit rate; boundary year to 3000

Plus similarly for the other currencies in the same temporary table...

1900-01-01  2017-10-04  JPY  0.00754761885  <-- rate guessed from the next; fictional date_from
2017-10-05  3000-01-01  JPY  0.00754761885  <-- explicit rate; fictional date_to

How can I efficiently construct such temporary table?

Do you have any other suggestions related to the problem?

Update: I have posted my solution to Code Review https://codereview.stackexchange.com/q/177517/16189 Please, have a look to find the flaws.

Answer 1

I don't think that you will need a temporary table.

You first need to get the rates that have the highest date_from value for every invoice. That is simply a MAX on date_from with the limitation of the rate's date being smaller than the invoice's date. For the example I used CZK as the currency to convert to:

SELECT 
  invoices.id
  , invoices.cur
  , MAX(date_from) AS current
FROM invoices
JOIN rates
ON rates.cur1 = invoices.cur
AND invoices.date > rates.date_from
AND rates.cur2 = 'CZK'
GROUP BY invoices.id, invoices.cur, invoices.date

Because of the limitations on columns available for SELECT caused by the GROUP BY we now have to join the two tables again and then join it with our effort of getting the current rate:

SELECT 
  invoices.id
  , invoices.cur
  , invoices.amount
  , 'CZK' AS otherCurrency
  , invoices.amount / rates.coef1 * rates.coef2 AS converted
FROM invoices
JOIN
  (SELECT 
    invoices.id
    , invoices.cur
    , MAX(date_from) AS current
  FROM invoices
  JOIN rates
  ON rates.cur1 = invoices.cur
  AND invoices.date > rates.date_from
  AND rates.cur2 = 'CZK'
  GROUP BY invoices.id, invoices.cur, invoices.date) AS current_rate
ON invoices.id = current_rate.id
JOIN rates
ON current_rate.current = rates.date_from
AND rates.cur1 = invoices.cur
AND rates.cur2 = 'CZK'

I prepared a fiddle to show the SQL in action.

Answer 2

Suppose the exchange rate table is as follows, with exchange rates to your target currency:

CREATE TABLE currency_rate (
    currency_id INT NOT NULL,
    update_date DATE NOT NULL,
    rate DECIMAL(18,6) NOT NULL,
    CONSTRAINT PK_currency_rate PRIMARY KEY(currency_id,update_date)
);

You can use a correlated subquery to link invoices to the exchange rate:

SELECT
    i.*,
    cr.rate
FROM
    invoice AS i
    INNER JOIN currency_rate AS cr ON
        cr.currency_id=i.currency_id AND
        cr.update_date=(
            SELECT
                MAX(cr_i.update_date)
            FROM
                currency_rate AS cr_i
            WHERE
                cr_i.currency_id=i.currency_id AND
                cr_i.update_date<=i.invoice_date
        );

---

If you do have a lot of invoices and a lot of rates, a solution based on a temporary table might improve performance. Best to measure which one wins. Based on the same currency_rate table definition:

CREATE TABLE #cr (
    date_from DATETIME,
    date_to DATETIME,
    currency_id INT,
    rate DECIMAL(18,6)
);
CREATE CLUSTERED INDEX IX_tcr_curr_dt ON #cr(currency_id,date_from);

INSERT INTO #cr (
    date_from,
    date_to,
    currency_id,
    rate
)
SELECT
    date_from=ISNULL(DATEADD(DAY,1,LAG(update_date) OVER (PARTITION BY currency_id ORDER BY update_date)), '17530101'),
    date_to=CASE WHEN LEAD(update_date) OVER (PARTITION BY currency_id ORDER BY update_date) IS NULL THEN '99991231' ELSE update_date END,
    currency_id,
    rate
FROM
    currency_rate AS cr;

SELECT
    i.*,
    c.rate
FROM
    invoices AS i
    INNER JOIN #cr AS c ON 
        c.currency_id=i.currency_id AND 
        c.date_from<=i.invoice_date AND 
        c.date_to>=i.invoice_date;

DROP TABLE #cr;