How to write a sql query for following business logic?

Question

I have three following three tables,

1. ItemProdCodeValueMapping (ItemId and ProdCd are part of primary key)

ItemId    ProdCd    ProdValue    ProdCurrencyCd
123       Shampoo   4.6          GBP           
124       Shampoo   5.8          GBP           
128       Shampoo   7.8          AUD           
125       Cashews   7.6          CAD           
125       ESwitch   17.6         CAD           
123       ESwitch   13.80        USD           

2. ItemDetails (ItemId is primary key)

ItemId    OrderDate
123       2019-04-15
124       2017-07-15
125       2016-05-25

3. ExchangeRates (This table has exchange rates with respect to USD, meaning exchange rates only in term of USD to other currencies)

SourceCurrency    TargetCurrency    ExchangeDate    ExchangeRate
USD               GBP               2017-01-23        0.8039  
USD               GBP               2017-01-24        0.8034
USD               GBP               2017-01-25        0.7942
USD               CAD               2017-01-23        1.10
USD               CAD               2017-01-24        1.12
USD               CAD               2017-01-25        1.18
USD               AUD               2017-01-23        1.10
USD               AUD               2017-01-24        1.12
USD               AUD               2017-01-25        1.18`

QUERY Expectation My client is providing me a prodCd and currency code e.g (Shampoo|AUD) and expecting to get result of all rows where prodCd is shampoo. IMP NOTE: If the ProdCd currency is different from requested currency, prodValues should convertd value.

Rules for conversion,

1. If ItemId's prodValue needs to be converted, use itemIds OrderDate from ItemDetails table and use ExchangeRates table to do the conversion (Join on OrderDate and Exchange Date)
2. (Part I don't know) If orderDate is not present in ExchangeRates table use the closest ExchangeRate. (Get Closest exchange date to the order date and use that exchange rate).
3. (Part I don't know) How can I write a query which covers all the conversion cases,
   3.1. Convert GBP product value to AUD? As all conversion rates are in terms of USD. I understand that I need to convert GBP to USD (Using USD to GBP) and then convert USD to AUD (using USD to AUD rate)
   3.2 User can request result in any currency. (Consider that exchange rate table has rates for all the currencies)

Here is what I got until now,

SELECT id.ItemId,    
       id.OfferingDate,     
       ipcv.Prodcd,     
Cast(Prodvalue AS DECIMAL(22, 8)) * er.ExchangeRate AS finalConvertedValue      
FROM   ExchangeRates er     
       JOIN ItemProdCodeValueMapping ipcv     
         ON er.SourceCurrency = ipcv.ProdCurrencyCd         
       JOIN Itemdetails id    
         ON ipcv.ItemId = id.ItemId
            AND id.OrderDate = er.ExchangeDate    
WHERE  er.TargetCurrency = 'AUD'     
       AND ProdCd = 'Shampoo'  

My end goal is to have a query which will cover all cases.

Answer 1

here how you can achieve this by using cross apply, second cross apply will give you the result in any provided currency:

if there is no provided currency exchange for given currency , the target currency would be in USD :

SELECT
    ipv.*
    , id.*
    , er.SourceCurrency
    , er.ExchangeDate
    , er.ExchangeRate
    , ipv.ProdValue * (1 / er.ExchangeRate)                    AS [ProdValue in USD]
    , er2.ExchangeDate [target exchnage date]
    , er2.ExchangeRate [target exchange rate]
    , ipv.ProdValue * (1 / ISNULL(er.ExchangeRate, 1)) * ISNULL(er2.ExchangeRate, 1) AS [ProdValue in target exchange]
FROM
    ItemDetail                    id
    JOIN ItemProdCodeValueMapping ipv
        ON ipv.ItemId = id.ItemId
    OUTER APPLY
    (
        SELECT TOP 1
            *
        FROM
            dbo.ExchangeRates AS er
        WHERE
            er.TargetCurrency = ipv.ProdCurrencyCd
            AND er.SourceCurrency = 'USD' -- to avoid converting USD
        ORDER BY
            ABS(DATEDIFF(DAY, ExchangeDate, id.OrderDate))
    ) er
    OUTER APPLY
    (
        SELECT TOP 1
            *
        FROM
            dbo.ExchangeRates AS er_target
        WHERE
            er_target.TargetCurrency = 'CAD'
        ORDER BY
            ABS(DATEDIFF(DAY, er_target.ExchangeDate, id.OrderDate))
    ) er2
WHERE
    ipv.ProdCd = 'Shampoo'

Answer 2

the other way of doing this, You could create a join with exchange table mapping target currency and get the same results

 DECLARE @Prodcd nvarchar(30);  
 DECLARE @TargeCurrency nvarchar(30);  

 SET @Prodcd = 'Shampoo ';
 SET @TargeCurrency = 'CAD';
 select ItemDetails.ItemId,Prodcd,ItemProdCodeValueMapping.ProdValue,
 ProdCurrencyCd,ItemProdCodeValueMapping.ProdValue,ItemDetails.Orderdate
    ,(ItemProdCodeValueMapping.ProdValue *(1/ExchangeRates.ExchangeRate)) * TargetCurrency.ExchangeRate finalConvertedValue   

 from ItemProdCodeValueMapping 

 inner join ExchangeRates on ExchangeRates.TargetCurrency = ItemProdCodeValueMapping.ProdCurrencyCd

 inner join ItemDetails on ItemProdCodeValueMapping.ItemId = ItemDetails.ItemId and ItemDetails.Orderdate = ExchangeRates.ExchangeDate

 inner join (select top 1 TargetCurrency,ExchangeRate,ExchangeDate from  ExchangeRates where 
    TargetCurrency = @TargeCurrency order by ExchangeDate desc) as   TargetCurrency  on TargetCurrency.ExchangeDate <= ItemDetails.Orderdate
 
 where Prodcd = @Prodcd