May 16, 2016 at 10:15 am
I have a table that has daily rates for various currencies in multiple rows. i.e. eur to USD for 1/1/2016, Eur to CAD for 1/1/2016 etc. I am trying to get one row with the "effective" exchange rate. so for example
1/1/2016 .800000 would be the rate for Canada on 1/1/2016. I think there are about 5 rates in the table, but right now I just need the CAD to USD. So end resulting formula is the 1/1/2016CADrate/1/1/2016USDrate. Anyway attached is a word document with the table insert statement and an example of the expected results.
I tried using SQL pivot function but did not get the answer I was looking for. Not sure if it was my code, or if Pivot is not appropriate way to build this solution.
May 16, 2016 at 10:37 am
randyetheridge (5/16/2016)
I have a table that has daily rates for various currencies in multiple rows. i.e. eur to USD for 1/1/2016, Eur to CAD for 1/1/2016 etc. I am trying to get one row with the "effective" exchange rate. so for example1/1/2016 .800000 would be the rate for Canada on 1/1/2016. I think there are about 5 rates in the table, but right now I just need the CAD to USD. So end resulting formula is the 1/1/2016CADrate/1/1/2016USDrate. Anyway attached is a word document with the table insert statement and an example of the expected results.
I tried using SQL pivot function but did not get the answer I was looking for. Not sure if it was my code, or if Pivot is not appropriate way to build this solution.
can you please confirm that all the "fromdates" are the same as "todates"
for quicker answers.... I would suggest reading this article on how to post questions with sample data...makes it easier for you an us
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 16, 2016 at 10:55 am
Yes as of now the from date and to date will be the same. one rate per day per currency.
May 16, 2016 at 11:04 am
maybe....
SELECT FromDate,
ToDate,
MAX(CASE
WHEN CurrencyCode = 'cad'
THEN ExchangeRate
END)
/
MAX(CASE
WHEN CurrencyCode = 'usd'
THEN ExchangeRate
END) AS yourcalc,
Companyid
FROM exchangerate
GROUP BY FromDate,
Companyid,
ToDate
HAVING(Companyid = 'Can01');
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 16, 2016 at 11:35 am
worked perfect. Now I just need to understand how it worked. thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply