title: Calculate column value based on criterias (values from both current row and a previous one)
body: <p>I am trying to learn some SQL by combining it with my interest in finance. At the moment I am attempting to calculate some portfolio data based on a list of transactions (in SQL Server).</p>
  <p>This is my table:</p>
  <pre><code>create table StockTransactions 
  (
      TransactionID int,
      Instrument varchar(32),
      TransactionType varchar(32),
      Units int,
      Price float,
      TransactionSum as case
          when TransactionType = 'Buy' then (-(Units * Price))
          when TransactionType = 'Sell'  then (Units * Price)
          else 0
          end,
      PurchaseCost float
  )
  </code></pre>
  <p>Sample data:</p>
  <pre><code>insert into StockTransactions (TransactionID, Instrument, TransactionType, Units, Price)
  values 
  (1, 'Apple', 'Buy', 10, 120),
  (2, 'Microsoft', 'Buy', 20, 290),
  (3, 'Apple', 'Buy', 10, 125),
  (4, 'Apple', 'Sell', 5, 140),
  (5, 'Apple', 'Buy', 10, 130),
  (6, 'Apple', 'Sell', 10, 135)
  </code></pre>
  <p>Which results in this table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>TransactionID</th>
  <th>Instrument</th>
  <th>TransactionType</th>
  <th>Units</th>
  <th>Price</th>
  <th>TransactionSum</th>
  <th>PurchaseCost</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Apple</td>
  <td>Buy</td>
  <td>10</td>
  <td>120</td>
  <td>-1200</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Microsoft</td>
  <td>Buy</td>
  <td>20</td>
  <td>290</td>
  <td>-5800</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Apple</td>
  <td>Buy</td>
  <td>10</td>
  <td>125</td>
  <td>-1250</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Apple</td>
  <td>Sell</td>
  <td>5</td>
  <td>140</td>
  <td>700</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>5</td>
  <td>Apple</td>
  <td>Buy</td>
  <td>10</td>
  <td>130</td>
  <td>-1300</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>6</td>
  <td>Apple</td>
  <td>Sell</td>
  <td>10</td>
  <td>135</td>
  <td>1350</td>
  <td>NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I am trying to calculate the values for the PurchaseCost column. The values I want are:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Row</th>
  <th>PurchaseCost</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>-1200</td>
  </tr>
  <tr>
  <td>2</td>
  <td>-5800</td>
  </tr>
  <tr>
  <td>3</td>
  <td>-2450</td>
  </tr>
  <tr>
  <td>4</td>
  <td>-1837.5</td>
  </tr>
  <tr>
  <td>5</td>
  <td>-3137.5</td>
  </tr>
  <tr>
  <td>6</td>
  <td>-2353.13</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The logic is as follows. If a purchase has been made, the TransactionSum should be added to the latest previous PurchaseCost for that instrument. However, when a sale has been made a certain sum should be subtracted.</p>
  <p>In transaction four I sell 5 of my 20 Apple shares. Therefor I'd like to subtract 1/4 of the previous PurchaseCost for a new PurchaseCost value of -1837.5.</p>
  <p>In transaction six I sell 10 of my 25 Apple shares, and would then like to reduce the PurchaseCost to 2353.13 (60% of the previous value).</p>
  <p>I can't seem to figure out how to do this by myself. Any ideas?</p>