First-in-first-out (FIFO) inventory costing

Question

Here's an interesting article that I found useful on my project:

Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem:

Stock table which we use to track the track movements of stock in and out of our imaginary stock warehouse. Our warehouse is initially empty, and stock then moves into the warehouse as a result of a stock purchase (tranCode = 'IN'), or due to a subsequent return (tranCode = 'RET'), and stock moves out of the warehouse when it is sold (tranCode = 'OUT'). Each type of stock tem is indentified by an ArticleID. Each movement of stock in or out of the warehouse, due to a purchase, sale or return of a given item, results in a row being added to the Stock table, uniquely identified by the value in the StockID identity column, and describing how many items were added or removed, the price for purchases, the date of the transaction, and so on.

Though I'm using this on my on-going project, Im stuck on how to get the price-to-charge on every transaction 'OUT'. I need to have this value to determine how much i will charge my customers.

1. First add 5 apples (each $10.00) to the stock, for a total of $50.00

2. Add 3 apples (each $20.00) to the stock total of 8 apples, for a total price of $110.00

3. Then take out 6 items (5 each $10.00 and 1 each $20.00) $70 total

4. After the transaction it will be leaving 2 apples @$20 each with a total of $40

---

 Here's my current table
 Item    transaction code    qty     price   
 apple   IN                    5     10.00    
 apple   IN                    3     20.00   
 apple   OUT                   6          

 Manual computation for the OUT transaction price (FIFO)
 QTY     price   total price 
 5       10.00   50.00 
 1       20.00   20.00 
 TOTAL:6         70.00 

 Output of the script:
 Item    CurrentItems   CurrentValue
 apple   2            40.00

 What I need:
 Item    transaction code    qty     price   CurrentItems    CurrentValue 
 apple   IN                    5     10.00   5               50.00 
 apple   IN                    3     20.00   8               110.00 
 apple   OUT                   6             2                   40.00 

 This too will be OK
 Item    transaction code    qty     price   CurrentItems    
 apple   IN                    5     10.00   0               
 apple   IN                    3     20.00   0                
 apple   OUT                   6         70 

The script posted that won the competition was very useful, I hope someone can help me on how to get the price per 'OUT' transaction

Answer 1

I suggest to design your table as below: Add a new field to your table, i.e. qty_out

The table before selling:

Item transaction code    qty     qty_out  price   
 apple   IN                    5    0        10.00    
 apple   IN                    3    0        20.00   
 apple   OUT                   6    null

And the table after selling the 6 items:

Item    transaction code    qty     qty_out  price   
 apple   IN                    5    5        10.00    
 apple   IN                    3    1        20.00   
 apple   OUT                   6    null

You can compare "qty" with "qty_out" (for IN transactions) to find out the price.