hist_qry2.sql

Adjusts all prices and volumes to reflect the split events during a specified 300-day period for a specified set of 1000 stocks, assuming that events occur before the first trade of the split date.

To determine the split-adjusted prices and volumes, prices are multiplied by the split factor and volumes are divided by the split factor.

Output

The results returned by this query are:

TRADING_SYMBOL  TRADE_DATE   H_PRC    L_PRC    C_PRC    O_PRC       VOL
AAA             2005-03-03  793.52   728      740.88   740.88   27.46428571
AAA             2005-03-04  749.84   651      726.04   740.88   29.10714286
AAA             2005-03-07  764.4    672      733.32   726.04   29.10714286
AAA             2005-03-08  786.24   637      726.04   733.32   26.17857143
AAA             2005-03-09  707      637      711.48   726.04   26.67857143
AAA             2005-03-10  700      624.96    697.2   711.48   24
AAA             2005-03-11  749      618.24    711.2    697.2   23.75
AAA             2005-03-14  742.56   658      732.48    711.2   25.85714286
AAA             2005-03-15  757.12   686       725.2   732.48   26.85714286
AAA             2005-03-16  728      644      710.64    725.2   28.46428571
AAA             2005-03-17  721      700      724.92   710.64   30.14285714
AAA             2005-03-18  735      644      717.64   724.92   28
AAA             2005-03-21  707      665      710.64   717.64   28
AAA             2005-03-22  763      637      710.64   710.64   27.71428571
AAA             2005-03-23  763      658.56   696.36   710.64   24.92857143
AAA             2005-03-24  692.16   638.4    682.36   696.36   25.14285714
AAA             2005-03-25  725.76   651.84   689.36   682.36   26.89285714
AAA             2005-03-28  728      624.96   703.08   689.36   26.89285714
AAA             2005-03-29  728      686      710.08   703.08   25
AAA             2005-03-30  700      672      710.08   710.08   23.25
AAA             2005-03-31  735      672      717.08   710.08   21.35714286
AAA             2005-04-01  735      651      724.36   717.08   21.75
AAA             2005-04-04  735      665      724.36   724.36   19.78571429
AAA             2005-04-05  756      644      724.36   724.36   19.35714286
AAA             2005-04-06  778.96   679      738.92   724.36   19.35714286
...

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:

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