hist_qry1.sql

Gets the closing price of a set of 10 stocks over a 10-year period, and groups the data into weekly, monthly, and yearly aggregates.

For each aggregate period, determines the low, high, and average closing price value. Output is sorted by TRADING_SYMBOL and trade date.

Note:

The hist_qry1 query uses the ROLLUP operator. The NULL values for month and week are subtotals. A NULL value in the week column is a subtotal for the month. A NULL value in the month and week columns is a subtotal for the year.

Output

The first 25 rows returned by this query are:

TRADING_SYMBOL  YEAR   MON    WEEK  MAX_PRICE  MIN_PRICE        AVG_PRICE
AAA             2005     2       7      26.24      25.48           25.925
AAA             2005     2       8      26.49      25.46           25.972
AAA             2005     2       9      27.28      26.48           26.746
AAA             2005     2      10      26.47      26.47            26.47
AAA             2005     2  (NULL)      27.28      25.46      26.25066667
AAA             2005     3      10      26.46      25.93          26.2625
AAA             2005     3      11      26.19       24.9           25.566
AAA             2005     3      12      26.16      25.38           25.792
AAA             2005     3      13      25.38      24.37           24.924
AAA             2005     3      14      25.61      25.11            25.36
AAA             2005     3  (NULL)      26.46      24.37      25.56086957
AAA             2005     4      14      25.87      25.87            25.87
AAA             2005     4      15      26.39      25.34           25.866
AAA             2005     4      16       26.1      25.07           25.634
AAA             2005     4      17      25.81      24.81           25.314
AAA             2005     4      18      27.94      26.59           27.284
AAA             2005     4  (NULL)      27.94      24.81      26.01714286
AAA             2005     5      19       27.1      26.55           26.828
AAA             2005     5      20      27.08      26.29            26.71
AAA             2005     5      21      27.08      26.54           26.862
AAA             2005     5      22      28.45      27.07           27.836
AAA             2005     5      23      27.88       27.6            27.74
AAA             2005     5  (NULL)      28.45      26.29      27.12090909
AAA             2005     6      23      27.05      26.24            26.69
...

SQL Statements

-- Get the closing price of a set of 10 stocks for a 10-year period and
-- group into weekly, monthly and yearly aggregates.
-- For each aggregate period determine the low, high and average closing
-- price value.
-- The output should be sorted by INSTRUMENT_ID and trade date.

commit
;

SELECT sh.TRADING_SYMBOL,
DATEPART(yy,sh.TRADE_DATE) AS YEAR,
DATEPART(mm,sh.TRADE_DATE) AS MON,
DATEPART(wk,sh.TRADE_DATE) AS WEEK, 
MAX(sh.CLOSE_PRICE) AS MAX_PRICE,
MIN(sh.CLOSE_PRICE) AS MIN_PRICE,
AVG(sh.CLOSE_PRICE) AS AVG_PRICE
FROM  STOCK_HISTORY sh 
WHERE
sh.TRADE_DATE BETWEEN '2005-02-08'
	and '2015-02-07'
and sh.TRADING_SYMBOL in ('AAA','AAB','AAC','AAD','AAE','AAF','AAG','AAH','AAI','AAJ' )
GROUP BY ROLLUP ( sh.TRADING_SYMBOL,
DATEPART(yy,sh.TRADE_DATE),
	 DATEPART(mm,sh.TRADE_DATE),
DATEPART(wk,sh.TRADE_DATE))
;