title: FIFO match first stock buys to first sells sql
body: <p>I have data that looks like this:</p>
  
  <p><a href="http://i.stack.imgur.com/ftI1Q.png" rel="nofollow">Stock buys and sells</a></p>
  
  <p>I need a query to apply the FIFO method to the Buys and Sells so I get a table that looks like this:
  <a href="http://i.stack.imgur.com/ao2Qm.png" rel="nofollow">FIFO Buys and Sells</a></p>
  
  <p>I want to be able to match the first buy/s to the first sells with buys on the left and sells on the right. If there is no sell then Nulls should be applied on the right and if there is no buy then nulls should be applied on the left. The brokerage transaction key can be used as the order in which the trades occurred. This is what I've tried so far. Any help would be much appreciated!</p>
  
  <pre><code>SELECT  a.ACCT_ID, a.Trade_Date_Key, a.Brokerage_Transaction_Key, a.Buy_Sell_Code, a.Principal_Amt, a.Security_Quantity
       , (a.Security_Quantity + b.Security_Quantity) CUMULATIVE_POSITION
       , a.SHARE_PRICE
       , (A.Principal_Amt + B.Principal_Amt) CUMULATIVE_VALUE
  from #TRANSACTIONS_WITH_RANK a
       left join #TRANSACTIONS_WITH_RANK b 
       on a.acct_id = b.acct_id and a.rank = b.rank + 1
  ORDER BY BROKERAGE_TRANSACTION_KEY
  </code></pre>