I am trying to come up with a query that takes two tables, one with non-split-adjusted historical stock prices, and one with information on splits, for instance:
CREATE TABLE quotes ( symbol VARCHAR(127) NOT NULL, date DATE NOT NULL, quote FLOAT NOT NULL, PRIMARY KEY (symbol, date), INDEX (date), ); INSERT quotes VALUES ("A", "2004-01-01", 3); INSERT quotes VALUES ("A", "2004-01-02", 3); INSERT quotes VALUES ("A", "2004-01-03", 3); INSERT quotes VALUES ("A", "2004-01-04", 3); INSERT quotes VALUES ("A", "2004-01-05", 2); INSERT quotes VALUES ("A", "2004-01-06", 2); INSERT quotes VALUES ("A", "2004-01-07", 2); INSERT quotes VALUES ("A", "2004-01-08", 1); INSERT quotes VALUES ("A", "2004-01-09", 1); CREATE TABLE splits ( symbol VARCHAR(127) NOT NULL, date DATE NOT NULL, split_from INT UNSIGNED NOT NULL, split_to INT UNSIGNED NOT NULL, PRIMARY KEY (symbol, date), ); INSERT splits VALUES ("A", "2004-01-05", 2, 3); INSERT splits VALUES ("A", "2004-01-08", 1, 2); I need to be able to pull out split-adjusted quotes, like this: SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = "A" ORDER BY date; +--------+------------+-------+----------------+ | symbol | date | quote | adjusted_quote | +--------+------------+-------+----------------+ | A | 2004-01-01 | 3 | 1 | | A | 2004-01-02 | 3 | 1 | | A | 2004-01-03 | 3 | 1 | | A | 2004-01-04 | 3 | 1 | | A | 2004-01-05 | 2 | 1 | | A | 2004-01-06 | 2 | 1 | | A | 2004-01-07 | 2 | 1 | | A | 2004-01-08 | 1 | 1 | | A | 2004-01-09 | 1 | 1 | +--------+------------+-------+----------------+ Split-adjusting means that on a split date all previous prices are multiplied by "split_from/split_to" ratio. In my example two splits took place, one on 2004-01-05, which multiplied all previous prices by 2/3 and another one on 2004-01-08, which multiplied all previous prices (including those already affected by first split) by 1/2. Any help would be appreciated. --- Fyodor Golos Stockworm, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]