ae15 ae15 - 5 months ago 8
SQL Question

Getting Exchange Rates for Historical and Using Today's Rate for Future Dates

There is a table A which has Start and End Date, Price and CurrencyCode.
There is a table B which has historical + Today's Exchange Rates.
I need to convert table A Price based on the following assumptions:


  • For the Historical Dates (I.e Start & End Date) < Today's Date, I
    will get their average exchange rate and use that for the conversion.

  • Any future Date (Date > Today) will use today's exchange rate.



How do I implement this. This doesn't seem like something I can do within a subquery. Am I correct to assume I would need a function for this?

How would I calculate this Start And End Date overlap today's date?
I.e :


  • Today's date is: July 5, 2016

  • Start Date is: April 6, 2016

  • End Date is: August 20, 2016.



How would create a function or subquery so that In this case for anything > today's date it would get the Current exchange rate and for anything historical it would get the data points and then Take the average.

What would the function look like. I am completely stumped.

TABLE A SCHEMA:


  • OrderItem

  • StartDate

  • EndDate

  • CurrencyCode



TABLE B SCHEMA:


  • ToCurrCode

  • FromCurrCode

  • Rate

  • Date



This is what I am trying right now to see what dates corresponds to what:

select a.* ,er.*
,CASE
WHEN a.CurrCode = @Curr THEN NULL
ELSE (er.Rate*1.00)
END as Rate
from
Table a
LEFT JOIN ExchangeRates er
ON (CASE
WHEN a.WeekStart >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)
AND er.[Date] = CAST(CAST(GETDATE() AS DATE) AS DATETIME) THEN 1
WHEN a.WeekEnd < CAST(CAST(GETDATE() AS DATE) AS DATETIME)
AND er.[Date] BETWEEN a.WeekStart AND a.WeekEnd THEN 1
END) = 1
AND a.CurrCode = er.FromCurrcode
AND er.ToCurrCode = 'CAD'
WHERE POOrderNO='45646'
AND a.Icode='35450'


/*GROUP BY vendor, POOrderNO, PODate, warehouseid, Icode, Description, Weeklyextended, qtyordered, a.rate, unitdiscountamount, daysinwk,
OutDate, InDate, qty, item_weeklyextended, MONTH, YEAR, WeekCounter, WeekStart, WeekEnd, a.currcode
Order by YEAR, Month, WeekCounter */


The Start and End Date correspond to WeekStart AND WeekEnd

Here is a screenshot.
http://screenshot.net/53qx1fd

Answer

Wihtout test data I am taking a little bit of guess but something like this should work....

    DECLARE @ToCurrCode INT

        SELECT
            a.OrderItem
            --,AVG(er.Rate*1.00) AS ExchangeRate
        ,CASE
           WHEN a.CurrencyCode = @ToCurrCode THEN a.Price
           ELSE AVG(er.Rate*1.00) * a.Price
        END AS ConvertedPrice
    FROM
        TableA a
        LEFT JOIN TableB er
        ON (CASE
              WHEN a.EndDate >= CAST(CAST(GETDATE() AS DATE) AS DATETIME)
                 AND er.[Date] = CAST(CAST(GETDATE() AS DATE) AS DATETIME) THEN 1
              WHEN a.EndDate < CAST(CAST(GETDATE() AS DATE) AS DATETIME)
                 AND er.[Date] BETWEEN a.StartDate AND a.EndDate THEN 1
           END) = 1
        AND a.CurrencyCode = er.FromCurrcode
        AND er.ToCurrCode = @ToCurrCode
    GROUP BY
        a.OrderItem
        ,a.CurrencyCode
        ,a.Price

Note if you are mixing DateTime and Date you may need to do some casting to drop off the time components to make one column = another..... Also you may not need to multiple Rate by 1.00 if already in a decimal format.