hist_qry4.sql

Calculates the value of the S&P 500 and Russell 2000 index for a specified day using unadjusted prices and the index composition of the two indexes on the specified day.

Output

The rows returned by this query are:

INDEX_NAME      AVERAGE_CLOSE_PRICE
Russell 2000       49.47026052
S&P500            54.44644

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:

-- Calculate the value of the S&P500 and Russell 2000 index
-- for a specified day using unadjusted prices and the index composition
-- of the 2 indexes (see appendix for spec) on the specified day.

commit
;

Select ii.INDEX_NAME, AVG(sh.CLOSE_PRICE) as AVERAGE_CLOSE_PRICE
FROM MARKET_INDEX AS ii 
inner join INDEX_CMPSTN AS ic
	on ii.MARKET_INDEX_ID = ic.MARKET_INDEX_ID 
inner join STOCK_HISTORY AS sh
on ic.INSTRUMENT_ID = sh.INSTRUMENT_ID
and sh.TRADE_DATE = '2005-03-03'
WHERE ii.INDEX_NAME in ('S&P 500','Russell 2000') 
GROUP BY
ii.INDEX_NAME
;