Simple SQL Stock Database Queries

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple SQL Stock Database Queries

Author

Topic

williamsr
Starting Member

2 Posts

Posted - 2012-11-29 : 22:58:36

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-30 : 00:33:32

[code]
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
[/code]

--
Chandu