title: SQL - Get cost of goods sold using FIFO & LIFO
body: <p>I'm trying to calculate the cost of goods sold so that I can later post it into the journal.
  My stock-card table looks like this:</p>
  
  <pre><code>warehouse_product_id    sc_date         qty     price   reserved_qty    left_qty    status
  3                       1/12/2014       2       100     0               2           debit
  3                       2/12/2014       2       200     0               2           debit
  3                       3/12/2014       10      200     0               10          debit
  </code></pre>
  
  <p>Let's say I want to <code>sell 3 items</code> with <code>warehouse_product_id = 3</code>, can anyone tell me how to query results below:</p>
  
  <p><strong>FIFO (First-In-First-Out)</strong></p>
  
  <pre><code>warehouse_product_id    sc_date         qty     price   reserved_qty    left_qty    status
  3                       1/12/2014       2       100     0               2           debit
  3                       2/12/2014       2       200     0               2           debit
  </code></pre>
  
  <p><strong>LIFO (Last-In-First-Out)</strong></p>
  
  <pre><code>warehouse_product_id    sc_date         qty     price   reserved_qty    left_qty    status
  3                       3/12/2014       10      200     0               10          debit
  </code></pre>
  
  <p>Another example, I want to <code>sell 5 items</code> with <code>warehouse_product_id = 3</code>, the resulted records should be like this:</p>
  
  <p><strong>FIFO (First-In-First-Out)</strong></p>
  
  <pre><code>warehouse_product_id    sc_date         qty     price   reserved_qty    left_qty    status
  3                       1/12/2014       2       100     0               2           debit
  3                       2/12/2014       2       200     0               2           debit
  3                       3/12/2014       10      200     0               10           debit
  </code></pre>
  
  <p><strong>LIFO (Last-In-First-Out)</strong></p>
  
  <pre><code>warehouse_product_id    sc_date         qty     price   reserved_qty    left_qty    status
  3                       3/12/2014       10      200     0               10          debit
  </code></pre>
  
  <p>Question:</p>
  
  <p>I want to get number of rows, that's if you sum up their <code>left_qty</code> it will be <code>&gt;=</code> than the requested qty, which are <code>3 and 5</code> in above example. So by using FIFO, I would get the top 2 and 3 rows, respectively. For LIFO, I only need the last record since it's sufficient to cover the requested qty.</p>
  
  <p>P.S. If possible I don't want to use a <code>Procedure</code>..</p>