May 20, 2016 at 8:42 am
I had posted this earlier and received a great response. At that time the data I had was one currency for one day. Now I will be getting the data in a different format. The USD will be daily, and the CAD will be monthly. The prior code actually works ok, but I am hoping for a better solution based upon the new parameters. I tried using a tool to create an output example but it did not work. So attached is the example of the results and the Create Table code.
Data table
CREATE TABLE masterdata.exchangeraterde (
FromDateDATETIME NOT NULL
,ExchangeRateNUMERIC(5,2) NOT NULL
,ToDateDATETIME NOT NULL
,CurrencyCodeVARCHAR(3) NOT NULL)
GO
INSERT INTO masterdata.exchangeraterde(FromDate,ExchangeRate,ToDate,CurrencyCode)
Values
('05/17/2016',88.3236,'05/17/2016','USD')
,('05/13/2016',88.1523,'05/16/2016','USD')
,('05/12/2016',87.7963,'05/12/2016','USD')
,('05/11/2016',87.7655,'05/11/2016','USD')
,('05/10/2016',88.0127,'05/10/2016','USD')
,('05/09/2016',87.804,'05/09/2016','USD')
,('05/06/2016',87.5274,'05/08/2016','USD')
,('05/04/2016',87.0777,'05/05/2016','USD')
,('05/03/2016',86.3483,'05/03/2016','USD')
,('05/02/2016',87.1156,'05/02/2016','USD')
,('04/30/2016',87.6962,'05/01/2016','USD')
,('04/29/2016',87.7578,'04/29/2016','USD')
,('03/31/2016',24.2872,'04/29/2016','BRL')
,('03/31/2016',67.8518,'04/29/2016','CAD')
,('04/28/2016',88.129,'04/28/2016','USD')
,('04/27/2016',88.4251,'04/27/2016','USD')
,('04/26/2016',88.6211,'04/26/2016','USD')
,('04/25/2016',88.8494,'04/25/2016','USD')
,('04/22/2016',88.739,'04/24/2016','USD')
,('04/21/2016',88.4173,'04/21/2016','USD')
,('04/20/2016',87.974,'04/20/2016','USD')
,('04/19/2016',88.2301,'04/19/2016','USD')
,('04/18/2016',88.4643,'04/18/2016','USD')
,('04/15/2016',88.6682,'04/17/2016','USD')
,('04/14/2016',88.8178,'04/14/2016','USD')
,('04/13/2016',88.4721,'04/13/2016','USD')
,('04/12/2016',87.6501,'04/12/2016','USD')
,('04/11/2016',87.7963,'04/11/2016','USD')
,('04/08/2016',87.974,'04/10/2016','USD')
,('04/07/2016',87.9353,'04/07/2016','USD')
,('04/06/2016',88.1135,'04/06/2016','USD')
,('04/05/2016',88.0359,'04/05/2016','USD')
,('04/04/2016',88.0049,'04/04/2016','USD')
,('04/01/2016',87.6271,'04/03/2016','USD')
,('03/31/2016',87.8349,'03/31/2016','USD')
,('03/30/2016',88.3236,'03/30/2016','USD')
,('02/29/2016',23.0447,'03/30/2016','BRL')
,('02/29/2016',67.7186,'03/30/2016','CAD')
,('03/29/2016',89.2459,'03/29/2016','USD')
,('03/24/2016',89.6781,'03/28/2016','USD')
,('03/23/2016',89.3575,'03/23/2016','USD')
,('03/22/2016',89.1266,'03/22/2016','USD')
,('03/21/2016',88.6997,'03/21/2016','USD')
,('03/18/2016',88.6839,'03/20/2016','USD')
,('03/17/2016',88.2924,'03/17/2016','USD')
,('03/16/2016',90.1713,'03/16/2016','USD')
,('03/15/2016',90.1226,'03/15/2016','USD')
,('03/14/2016',90.0333,'03/14/2016','USD')
,('03/11/2016',90.0414,'03/13/2016','USD')
,('03/10/2016',91.1245,'03/10/2016','USD')
,('03/09/2016',91.1245,'03/09/2016','USD')
,('03/08/2016',90.7853,'03/08/2016','USD')
,('03/07/2016',91.3242,'03/07/2016','USD')
,('03/04/2016',91.0498,'03/06/2016','USD')
,('03/03/2016',91.9118,'03/03/2016','USD')
,('03/02/2016',92.1489,'03/02/2016','USD')
,('03/01/2016',91.954,'03/01/2016','USD')
,('02/29/2016',91.8442,'02/29/2016','USD')
,('02/26/2016',90.7194,'02/28/2016','USD')
,('01/31/2016',65.0915,'02/28/2016','CAD')
,('01/31/2016',22.5764,'02/28/2016','BRL')
,('02/25/2016',90.6536,'02/25/2016','USD')
,('02/24/2016',91.2492,'02/24/2016','USD')
,('02/23/2016',90.9753,'02/23/2016','USD')
,('02/22/2016',90.629,'02/22/2016','USD')
,('02/19/2016',90.0739,'02/21/2016','USD')
,('02/18/2016',90.1144,'02/18/2016','USD')
,('02/17/2016',89.7263,'02/17/2016','USD')
,('02/16/2016',89.6379,'02/16/2016','USD')
,('02/15/2016',89.3575,'02/15/2016','USD')
,('02/12/2016',88.6839,'02/14/2016','USD')
,('02/11/2016',88.2068,'02/11/2016','USD')
,('02/10/2016',88.8099,'02/10/2016','USD')
,('02/09/2016',89.1822,'02/09/2016','USD')
,('02/05/2016',89.3336,'02/08/2016','USD')
,('02/04/2016',89.5015,'02/04/2016','USD')
,('02/03/2016',91.5583,'02/03/2016','USD')
,('02/02/2016',91.5667,'02/02/2016','USD')
,('02/01/2016',92.0217,'02/01/2016','USD')
,('01/31/2016',91.5751,'01/31/2016','USD')
,('01/29/2016',91.6254,'01/30/2016','USD')
,('12/31/2015',23.1927,'01/30/2016','BRL')
,('12/31/2015',66.1551,'01/30/2016','CAD')
,('01/28/2016',91.6086,'01/28/2016','USD')
,('01/27/2016',91.9625,'01/27/2016','USD')
,('01/26/2016',92.3958,'01/26/2016','USD')
,('01/25/2016',92.4471,'01/25/2016','USD')
,('01/22/2016',92.3276,'01/24/2016','USD')
,('01/21/2016',91.7095,'01/21/2016','USD')
,('01/20/2016',91.6758,'01/20/2016','USD')
,('01/19/2016',92.0556,'01/19/2016','USD')
,('01/18/2016',91.8021,'01/18/2016','USD')
,('01/15/2016',91.7347,'01/17/2016','USD')
,('01/14/2016',91.4829,'01/14/2016','USD')
,('01/13/2016',92.4044,'01/13/2016','USD')
,('01/12/2016',92.1234,'01/12/2016','USD')
,('01/11/2016',91.76,'01/11/2016','USD')
,('01/08/2016',91.9709,'01/10/2016','USD')
,('01/07/2016',92.0302,'01/07/2016','USD')
,('01/05/2016',92.9714,'01/06/2016','USD')
,('01/04/2016',91.6003,'01/04/2016','USD')
,('12/31/2015',91.8527,'01/03/2016','USD')
May 20, 2016 at 9:39 am
You did a great job posting ddl and sample data. However, you didn't provide any details about what you want to happen here. Can you explain what query you trying to write?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2016 at 10:04 am
did you open the attached word document? It gives a detailed example of the output file. someone gave me instructions on how to post the output, using a tool and CVS file. but I could not get it to work, so I posted it in the word document for now.
this is what I have so right now, but it really does not do what I want cleanly
SELECT ToDate
,Max(CASE
WHEN CurrencyCode = 'cad'
THEN ExchangeRate
END)
/
Max(CASE
WHEN CurrencyCode = 'usd'
THEN ExchangeRate
END) AS CADDailyExchRate
FROM masterdata.ExchangeRatesrde
GROUP BY ToDate
May 20, 2016 at 10:18 am
how are you calculating these results....are they supposed to be averages over the period?...or some other process?
End Result
FromDateToDate CADRate USDRateCADExchangeRate
3/31/20164/29/201667.851887.757877.3171
2/29/20163/30/201667.718688.323676.6710
1/31/20162/28/201665.091590.719471.7504
12/31/20151/30/201566.155191.575172.2414
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 20, 2016 at 10:36 am
randyetheridge (5/20/2016)
did you open the attached word document?
No I didn't. I don't usually open Office documents from people I don't know. Too many nasty things can get hidden in them. But it seems like JLS may have posted your desired results.
I don't understand the last column in your output at all. Where do those values come from?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2016 at 10:58 am
randyetheridge (5/20/2016)
did you open the attached word document? It gives a detailed example of the output file. someone gave me instructions on how to post the output, using a tool and CVS file. but I could not get it to work, so I posted it in the word document for now.this is what I have so right now, but it really does not do what I want cleanly
SELECT ToDate
,Max(CASE
WHEN CurrencyCode = 'cad'
THEN ExchangeRate
END)
/
Max(CASE
WHEN CurrencyCode = 'usd'
THEN ExchangeRate
END) AS CADDailyExchRate
FROM masterdata.ExchangeRatesrde
GROUP BY ToDate
this code I presume came from my answer to another post made by you
http://www.sqlservercentral.com/Forums/FindPost1786601.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 20, 2016 at 12:07 pm
Yes that was from an earlier post, where the original file was going to be a daily currency rate. Now the file will be monthly for CAD and daily for USD. Hence my re-post
May 20, 2016 at 12:15 pm
After thinking about this, I have changed by mind. I think a better solution might be a daily rate where the CAD from 12/31/2015 - 1/30/2016 is then divided by the daily USD. So maybe this would be a better solution
12/31/2015 CAD 66.1551/91.8527
1/1/2016 CAD 66.1551/91.8527
1/2/2016 CAD 66.1551/91.8527
1/3/2016 CAD 66.1551/91.8527
1/4/2016 CAD 66.1551/ 91.6003
1/5/2016 CAD 66.1551/92.9714
Now that I think about it, I like this solution much better. Keep the CAD rate the same for the CAD fromdate - todate and divide by the individual daily USD rate.
Any help doing this will be appreciated.
May 20, 2016 at 1:02 pm
ok I am still trying to get my result table to publish correctly. still not working. attached is a word document with the output updated to what I am now looking to try and create.
May 20, 2016 at 5:08 pm
when you post you will see at the lefthand side there is list of "IFCode Shortcuts" such as code=sql - /code (in square brackets) ..... if you look carefully these are in pairs
to use these to post TSQL code for example , highlight the relevant text that is TSQL and click on the code=sql - /code "pair"...you will see the code brackets surrounding your highlighted text.
Alternatively...click the code pair...they will automatically appear in your post, and then paste between them
Have a play to see how each pair effects the formatting by previewing your post (Click preview button at bottom of page)....you dont have to post it to see results
ps...always use the "url pair" for links to other webpages / urls
To get your Word doc results as below, I now use
http://www.tablesgenerator.com/text_tables with unicode option...I cut and paste from your Word doc and posted below as "code plain" pair
you can just as easily cut and paste from SSMS results, excel, notepad etc
+---------------------------------------------------------------+
¦ FromDate ¦ ToDate ¦ CADRate ¦ USDRate ¦ CADExchangeRate ¦
¦------------+------------+---------+---------+-----------------¦
¦ 4/29/2016 ¦ 4/29/2016 ¦ 67.8518 ¦ 87.7578 ¦ 77.3171 ¦
¦------------+------------+---------+---------+-----------------¦
¦ Etc ¦ etc ¦ ¦ ¦ ¦
¦------------+------------+---------+---------+-----------------¦
¦ 4/3/2016 ¦ 4/3/2016 ¦ 67.8518 ¦ 87.6271 ¦ 77.4324 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 4/2/2016 ¦ 4/2/2016 ¦ 67.8518 ¦ 87.6271 ¦ 77.4324 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 4/1/2016 ¦ 4/1/2016 ¦ 67.8518 ¦ 87.6271 ¦ 77.4324 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 3/31/2016 ¦ 3/31/2016 ¦ 67.8518 ¦ 87.8349 ¦ 77.2492 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 3/30/2016 ¦ 3/30/2016 ¦ 67.7186 ¦ 88.3236 ¦ 76.6710 ¦
¦------------+------------+---------+---------+-----------------¦
¦ etc ¦ Etc ¦ ¦ ¦ ¦
¦------------+------------+---------+---------+-----------------¦
¦ 3/2/2016 ¦ 3/2/2016 ¦ 67.7186 ¦ 92.1489 ¦ 73.4882 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 3/1/2016 ¦ 3/1/2016 ¦ 67.7186 ¦ 91.540 ¦ 73.9771 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 2/29/2016 ¦ 2/29/2016 ¦ 67.7186 ¦ 91.8442 ¦ 73.7320 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 2/28/2016 ¦ 2/28/2016 ¦ 65.0915 ¦ 90.7194 ¦ 71.7504 ¦
¦------------+------------+---------+---------+-----------------¦
¦ etc ¦ etc ¦ ¦ ¦ ¦
¦------------+------------+---------+---------+-----------------¦
¦ 2/1/2016 ¦ 2/1/2016 ¦ 65.0915 ¦ 92.0217 ¦ 70.7349 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/31/2016 ¦ 1/31/2016 ¦ 65.0915 ¦ 91.5751 ¦ 71.0799 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/30/2016 ¦ 1/30/2016 ¦ 66.1551 ¦ 91.6254 ¦ 72.2017 ¦
¦------------+------------+---------+---------+-----------------¦
¦ etc ¦ Etc ¦ ¦ ¦ ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/6/2016 ¦ 1/6/2016 ¦ 66.1551 ¦ 92.9714 ¦ 71.1564 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/5/2016 ¦ 1/5/2016 ¦ 66.1551 ¦ 92.9714 ¦ 71.1564 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/4/2016 ¦ 1/4/2016 ¦ 66.1551 ¦ 91.6003 ¦ 72.2215 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/3/2016 ¦ 1/3/2016 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/2/2016 ¦ 1/2/2016 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 1/1/2016 ¦ 1/1/2016 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦
¦------------+------------+---------+---------+-----------------¦
¦ 12/31/2015 ¦ 12/31/2015 ¦ 66.1551 ¦ 91.8527 ¦ 72.0230 ¦
+---------------------------------------------------------------+
anyways and before I forget
does this help you on your way to your answer....maybe one way of doing it....good luck
WITH USD as (
SELECT CurrencyCode, FromDate, ToDate, ExchangeRate
FROM exchangeraterde
WHERE (CurrencyCode = 'USD')
)
,
CAD as (
SELECT CurrencyCode, FromDate, ToDate, ExchangeRate
FROM exchangeraterde
WHERE (CurrencyCode = 'CAD')
)
SELECT USD.FromDate,
USD.ToDate,
CAD.ExchangeRate AS CADrate,
USD.ExchangeRate AS USDRate,
CAST((CAD.ExchangeRate / USD.ExchangeRate) * 100 as decimal(10,4)) as CADExchangeRate
FROM USD
LEFT OUTER JOIN CAD ON USD.FromDate >= CAD.FromDate
AND USD.ToDate <= CAD.ToDate
WHERE CAD.ExchangeRate > 0
ORDER BY USD.FromDate DESC;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 23, 2016 at 6:04 am
first, I will practice your two examples thanks. Second the code worked perfectly. I will research the WITH statement to understand your solution. Seems very similar to the union statement I was trying to use (which did not work) but of course yours worked. Thank you very much.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply