title: Accessing data from multiple rows
body: <p>So I am having some troubles writing a sql query. It is a financial stock problem and we have two table, one named A and one named B. The dates are divided into periods, so we want to calculate the investment value for the next period based on some criteria of the current period.</p>
  
  <p>For instance, to calculate the investment value of period 2, we first need to compare the price of stock of the first date of period 1 from table A with the strike price of the same date from table B, and then take the max price and divide it by the investment of first date which is given as 10,000. After that, you just simply do 10000/max(price,strike), and then multiply the value by 10000. I know how to get the max which can be done by either using CASE or max, but the difficulty I am facing is that how can I actually get the investment value of the previous period. The example above is an exception because we know the value of the first day. However, if you want to calculate the investment value for period 3, you will first need the value of period 2, and this is where I am stuck at.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Table A<br>
  <code>Date    Price<br>
  1/16/15  206 
  2/20/15  208
  3/20/15  205</code></p>
  
  <p>Table B</p>
  
  <p><code>Date    Strike<br>
  1/16/15  195 
  2/20/15  201
  3/20/15  206</code></p>
  
  <p>For example, the number of shares on 2/20/2015 is 10000(206) = 48.54
  And the investment value is 48.54 * 208 = 10096.
  206 is the max of 206 and 195, and 208 is the max of 208 and 201.</p>
  
  <p>Thanks in advance!</p>