Calculate Exchange Rates in MS SQL from Currency table with effective date clause

Question

I have a Currency table with the following structure where currency rates for each transaction currency are maintained in reference currency i.e. EUR but not necessarily in other currencies.

bcur|curr | effectivedt|Expressinbase|rate
EUR |SAR  |01/04/2020  |1        |12.23
EUR |SAR  |01/05/2020  |1        |12.27 
EUR |SAR  |01/06/2020  |1        |12.29
EUR |INR  |01/04/2020  |1        |77.78
EUR |INR  |01/05/2020  |1        |77.90
EUR |USD  |01/04/2020  |1        |1.34
EUR |GBP  |01/04/2020  |1        |23
EUR |GBP  |01/05/2020  |1        |32
USD |SAR  |01/04/2020  |1        |45
USD |SAR  |01/05/2020  |1        |54
USD |GBP  |01/04/2020  |2        |0.83
INR |SAR  |01/04/2020  |1        |80
.
.

I am selecting my sales order table for some amounts and now I need to convert the amount in transaction currency to EUR, USD and INR as per the transaction date in the Sales order table.

When transaction currency = the currency under consideration assign the amount accordingly
When transaction currency <> the currency then
    if the relation exists is currency table.
        If the rate is maintaied with Expressinbase = 1 then Amount in <curr> = Sales Order Amount / rate
        If the rate is maintaied with Expressinbase = 2 then Amount in <curr> = Sales Order Amount * rate
    If no direct relation is maintained (for USD or INR)
        Amount in <USD or INR> = Amount in EUR/ rate of EUR to <USD or INR>

This is quite helpful for the logic but I am still stuck at getting only 1 record from currency table as per transaction date

Implicitly Calculate Exchange Rates in SQL from Exchange Rate table

Joaquín Joaquín · Accepted Answer · 2020-06-12T12:31:39

I dont know what's your orders structure is but you should be able to solve it a similar way to this, just adjust to your conditions

IF OBJECT_ID(N'tempdb..#currency', N'U') IS NOT NULL   
DROP TABLE #currency

create table #currency (bcurr VARCHAR(3), curr VARCHAR(3), effectivedt DATETIME, Expressinbase MONEY, rate MONEY)

INSERT INTO #currency VALUES ( 'EUR', 'SAR', '20200401' ,1 ,12.23)
INSERT INTO #currency VALUES ( 'USD', 'SAR', '20200401' ,1 ,45)
INSERT INTO #currency VALUES ( 'INR', 'SAR', '20200401' ,1 ,80)

IF OBJECT_ID(N'tempdb..#orders', N'U') IS NOT NULL   
DROP TABLE #orders

create table #orders (id int IDENTITY(1,1), description VARCHAR(500), price MONEY, currency VARCHAR(3), order_date DATETIME)
INSERT INTO #orders VALUES('first order', 23.4, 'SAR', '20200401')
INSERT INTO #orders VALUES('second order', 15.4, 'SAR', '20200401')
INSERT INTO #orders VALUES('third order', 80.4, 'SAR', '20200401')

;with prices as (select o.id, o.description, o.price, o.currency,
       priceusd = ( CASE WHEN c.bcurr = 'USD' THEN o.price / c.rate else 0 END),
       priceeur = ( CASE WHEN c.bcurr = 'EUR' THEN o.price / c.rate else 0 END),
       princeinr = ( CASE WHEN c.bcurr = 'INR' THEN o.price / c.rate else 0 END)
from #orders o 
inner join #currency c on o.currency = c.curr and o.order_date = c.effectivedt)
select id, description, price, currency, priceusd= SUM(priceusd),  priceeur = SUM(priceeur), priceinr = SUM(princeinr)
from prices 
group by id, description, price, currency

Calculate Exchange Rates in MS SQL from Currency table with effective date clause

1 Answers