SQL

The following script contains the SQL statements for this query:
-- Adjust all prices and Volumes (prices are multiplied by the split factor
-- and Volumes are divided by the split factor) for a set of 1000 stocks to
-- reflect the split events during a specified 300 day period, assuming that
-- events occur before the first trade of the split date.
-- These are called split-adjusted prices and Volumes.

commit
;

SELECT B.TRADING_SYMBOL, TRADE_DATE,
B.HIGH_PRICE * IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) H_PRC,
B.LOW_PRICE * IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) L_PRC,
B.CLOSE_PRICE * IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) C_PRC,
B.OPEN_PRICE *IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) O_PRC,
B.Volume/IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) VOL
FROM STOCK_HISTORY AS B 
	left outer join SPLIT_EVENT A
	on B.INSTRUMENT_ID = A.INSTRUMENT_ID 
	AND B.TRADE_DATE < A.EFFECTIVE_DATE
WHERE  B.TRADING_SYMBOL BETWEEN 'AAA' AND 'BML'
	AND LENGTH(B.TRADING_SYMBOL) = 3
	and B.TRADE_DATE BETWEEN '2005-03-03'
	and '2005-12-03' 
GROUP BY B.TRADING_SYMBOL, 
TRADE_DATE ,
B.HIGH_PRICE,
B.LOW_PRICE,
B.CLOSE_PRICE, 
B.OPEN_PRICE,
B.Volume
;