Use last available currency rate by date available to calculate totals

Question

I am trying to convert currency rates on orders. The currency rates table is updated daily End of day for that particular day, however an order can be created earlier in the day, and as a result the rate will not be reflected with the current query. How would I add case statements and modify the query where by if the currency rate for the day does not exist use the last currency date available (within the table).

@Curr is the desired currency code. Could be 'USD', 'GBP' etc.

SELECT OrderNumber
  ,(CASE WHEN @Curr<>cur.code THEN 
    o.price * (SELECT Rate FROM xchangeRates xr 
                WHERE xr.FromCurrCode = c.Code 
                AND xr.ToCurrCode = @Curr 
                AND xr.Date= ISNULL((SELECT TOP 1 CAST(Crtd_DateTime AS Date) FROM ApDoc apdoc 
                                        WHERE apdoc.PONbr = o.OrderNumber AND apdoc.PONbr>'' 
                                        ORDER BY apdoc.Crtd_DateTime DESC),o.orderdate)
               )
    ELSE o.price
END ) as o.price
from orders o
join currency c on c.curcode = o.curcode

Why a CASE? Why not a subquery that returns the top 1 currency rate ordered by date DESC? — Tab Alleman
Or a subquery....but how would I use that If the current exchange rate is unavailable. — user2684009

Tab Alleman Tab Alleman · Accepted Answer · 2015-03-06T16:23:44

Why not this:

...
 o.price * (SELECT TOP 1 Rate FROM xchangeRates xr 
                WHERE xr.FromCurrCode = c.Code 
                AND xr.ToCurrCode = @Curr 
                ORDER BY xr.Date DESC)
...

If there are future dates in the xchangeRates table, all you have to do is add an additional filter to the WHERE clause to limit xr.Date to <= today.

EDIT: to handle this requirement:

if the invoice was created in the apdoc the it uses that Date for the exchange rate, but if not then it uses the date the order was created.

forget your subselect to apDoc and JOIN it to orders in the outer query instead:

LEFT OUTER JOIN apDoc 
ON apdoc.PONbr = o.OrderNumber

And then do this for your subquery instead of what I have above:

 o.price * (SELECT TOP 1 Rate FROM xchangeRates xr 
                WHERE xr.FromCurrCode = c.Code 
                AND xr.ToCurrCode = @Curr
                AND xr.Date =< COALESCE(CAST(apdoc.Crtd_DateTime AS Date),o.OrderDate)
                ORDER BY xr.Date DESC)

nb: I CAST Crtd_DateTime AS Date because you did it in your code, and for all I know it's a varchar, but if it's a Datetime datatype, then the cast isn't necessary in my solution.

Use last available currency rate by date available to calculate totals

1 Answers