SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding Groups in a select statement (help)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jcarrallo
Starting Member

Spain
23 Posts

Posted - 07/08/2014 :  16:58:32  Show Profile  Reply with Quote
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 Percentage
WITH PriceMonthly_CTE
AS
(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

USA
36636 Posts

Posted - 07/08/2014 :  17:02:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jcarrallo
Starting Member

Spain
23 Posts

Posted - 07/08/2014 :  17:37:05  Show Profile  Reply with Quote
ok, thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000