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
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