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
 Simple SQL Stock Database Queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

williamsr
Starting Member

2 Posts

Posted - 11/29/2012 :  22:58:36  Show Profile  Reply with Quote
Hi all,

I am having trouble with a couple of queries for a stock database that consists of tuples of daily stock data for 60 stocks over a number of years.

I am having trouble with two queries I really want to be able to work with and show, if you are able to help me with them I would be very appreciative.

The table is set up as follows:
Table:
Stocks

Columns:
("Sector","Symbol","Day","Month","Year","Open","High","Low","Close","Volume","AdjustedClose")

The first one is which month of the year are stock prices the lowest? Here is where I have gotten to with this query(I know it doesn't quite work):

SELECT s1.year, s1.month, MIN(avgclose)
FROM stocks s1 JOIN (
Select AVG(close) AS avgclose, year, month
FROM stocks
GROUP BY year, month) s2
ON s1.year=s2.year AND s1.month=s2.month
WHERE s1.year BETWEEN '2007' AND '2012';

The second one is to find which stocks have shown the biggest gain in closing price over the period of January 1, 2007 to November 1, 2012? (the change in closing price from the first day to the last day)

Any help is greatly appreciated!

-Rob

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 11/30/2012 :  00:33:32  Show Profile  Reply with Quote

1)
Select TOP 1 WITH TIES MIN([close]) AS LowestPrice, [year], [month]
FROM @tab
GROUP BY [year], [month]
ORDER BY LowestPrice

2)Find which stocks have shown the biggest gain in closing price over the period of January 1, 2007 to November 1, 2012? (the change in closing price from the first day to the last day)

;with cte as 
(
	SELECT Sector, Symbol, [Close], ROW_NUMBER() OVER(PARTITION by symbol ORDER BY [year], DATEPART(mm,CAST([month]+ ' 1900' AS DATETIME)), [day]) rn 
	FROM @tab
	WHERE CAST ( cast([year] AS CHAR(4)) + ' ' + [month] + ' '  + CAST([day] AS VARCHAR(2))  AS DATE) BETWEEN '2007-01-01' AND '2012-11-01'
)
SELECT TOP 1 WITH TIES t2.Symbol, t1.[Close] - t2.[Close] maxDiff
FROM cte t1 
JOIN cte t2 ON t1.rn = t2.rn+1 and t1.Symbol = t2.Symbol
ORDER BY maxDiff DESC


--
Chandu
Go to Top of Page

williamsr
Starting Member

2 Posts

Posted - 11/30/2012 :  01:08:45  Show Profile  Reply with Quote
Thanks for the help!!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2169 Posts

Posted - 11/30/2012 :  01:20:59  Show Profile  Reply with Quote
quote:
Originally posted by williamsr

Thanks for the help!!


Welcome

--
Chandu
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.09 seconds. Powered By: Snitz Forums 2000