ae15 - 1 year ago 59
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
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

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download