hist_qry1.sql

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

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

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
...

Note:

The output displayed in this section is derived by running the query with the large set of sample data provided in the Sybase RAP Samples component.

SQL Statements

The SQL statements for this query are:

-- 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))
;