Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jcarrallo
Starting Member
35 Posts |
 Posted - 2014-07-08 : 16:58:32
|
| Hi,Thanks for helping!The select statement below works fine but just to a limited extend.There is just one table (prices) with 3 fields (ticker, close, date).I use this select statement to get monthly returns for 1 ticker.My question is: can I get this same statement for multiple tickers? I guess I have to group by ticker but I wouldn´t know how to do it.Can anyone help?Many thanks, jay_______________________--Get Monthly Returns As PercentageWITH PriceMonthly_CTEAS(select row_number() OVER (ORDER BY DATE_) AS Period, ticker, close_, datepart(yyyy,date_) AS YEAR, datepart(mm,date_) AS MONTH from Prices where date_ IN ( --get last pricedate of each period select MAX(Date_) as a FROM Prices group by datepart(yyyy,date_),datepart(mm,date_) ))select a.Year, a.Month, CONVERT(NUMERIC(18,4),100*(a.Close_- b.Close_)/b.Close_) as [Monthly Return Percent], a.Close_ AS [Closing Price] from PriceMonthly_CTE a JOIN PriceMonthly_CTE b ON a.Period = b.Period+1 WHERE a.Year >=2010 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-08 : 17:02:48
|
| http://www.sqlservercentral.com/articles/Best+Practices/61537/Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jcarrallo
Starting Member
35 Posts |
Posted - 2014-07-08 : 17:37:05
|
| ok, thanks |
|
|
|
|
|
|
|
|
Adding Groups in a select statement (help)