StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCalculating from self-join
text
Body
copied!<p>I have a list of values and dates for stock ticker symbols and want to calculate quarterly returns in SQL. </p> <pre><code>CREATE TABLE `symbol_details` ( `symbol_header_id` INT(11) DEFAULT NULL, `DATE` DATETIME DEFAULT NULL, `NAV` DOUBLE DEFAULT NULL, `ADJ_NAV` DOUBLE DEFAULT NULL) </code></pre> <p>For fixed quarter start and end dates that works fine:</p> <pre><code>set @quarterstart='2008-12-31'; set @quarterend='2009-3-31'; select sha, (100*(aend-abegin)/abegin) as q1_returns from (select symbol_header_id as sha, ADJ_NAV as abegin from symbol_details where date=@quarterstart) as a, (select symbol_header_id as she, ADJ_NAV as aend from symbol_details where date=@quarterend) as b where sha=she; </code></pre> <p>This calculates all quarterly returns for all symbols. Sometimes that end of the quarter is a non-trading day, or the stock ceased operation, so I want to get the closest start and end dates to the quarter start and end dates.</p> <p>The solution would be to somehow get only the one start and one end of quarter value for each symbol_header_id by some GROUP BY statement, like (1)</p> <pre><code>SET @quarterstart = '2009-03-01'; SET @quarterend = '2009-4-31'; SELECT symbol_header_id, DATE, ADJ_NAV AS aend FROM symbol_details WHERE DATE BETWEEN @quarterstart AND @quarterend AND symbol_header_id BETWEEN 18540 AND 18550 GROUP BY symbol_header_id asc; </code></pre> <p>This gives the ADJ_NAV value for the date closest to the start of the quarter for each of the symbol_header_id values.</p> <p>So finally, (2)</p> <pre><code>SET @quarterstart = '2008-12-31'; SET @quarterend = '2009-3-31'; SELECT sh1, a.date, b.date, aend, abegin, (100*(aend-abegin)/abegin) AS quarter_returns FROM (SELECT symbol_header_id sh1, DATE, ADJ_NAV AS abegin FROM symbol_details WHERE DATE BETWEEN @quarterstart AND @quarterend GROUP BY symbol_header_id DESC) a, (SELECT symbol_header_id sh2, DATE, ADJ_NAV AS aend FROM symbol_details WHERE DATE BETWEEN @quarterstart AND @quarterend GROUP BY symbol_header_id ASC) b WHERE sh1 = sh2; </code></pre> <p>should calculate the quarterly returns for each symbols.</p> <p>Unfortunately this does not work. For some reason when I restrict the IDs like in (1) the correct start and end dates are used, but when the "AND symbol_header_id BETWEEN 18540 AND 18550" statements are removed, the same start and end dates come up. Why????</p> <p>The answer with unrolled JOINs is:</p> <pre><code>SET @quarterstart = '2008-12-31'; SET @quarterend = '2009-3-31'; SELECT tq.sym AS sym, (100*(alast.adj_nav - afirst.adj_nav)/afirst.adj_nav) AS quarterly_returns FROM -- First, determine first traded days ("ftd") and last traded days -- ("ltd") in this quarter per symbol (SELECT symbol_header_id AS sym, MIN(DATE) AS ftd, MAX(DATE) AS ltd FROM symbol_details WHERE DATE BETWEEN @quarterstart AND @quarterend GROUP BY 1) tq JOIN symbol_details afirst -- Second, determine adjusted NAV for "ftd" per symbol (see WHERE) ON afirst.DATE BETWEEN @quarterstart AND @quarterend AND afirst.symbol_header_id = tq.sym JOIN -- Finally, determine adjusted NAV for "ltd" per symbol (see WHERE) symbol_details alast ON alast.DATE BETWEEN @quarterstart AND @quarterend AND alast.symbol_header_id = tq.sym WHERE afirst.date = tq.ftd AND alast.date = tq.ltd; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload