Calculating Cost of Goods Sold

Question

I have two tables, one for incoming inventory and one for outgoing inventory that look more or less like this:

               purchase (incoming)
--------------+----------+-------+-----------
 inventory_id | quantity | price | timestamp 
--------------+----------+-------+-----------
 bobble       |        1 | $1.00 | 2014-01-01
 trinket      |        2 | $1.00 | 2014-01-02
 trinket      |        2 | $2.00 | 2014-01-03
--------------+----------+-------+-----------


               sale (outgoing)
--------------+----------+-------+-----------
 inventory_id | quantity | price | timestamp
--------------+----------+-------+-----------
 trinket      |        1 | $3.00 | 2014-01-04
 bobble       |        1 | $3.00 | 2014-01-05
 trinket      |        2 | $3.00 | 2014-01-06
 trinket      |        1 | $3.00 | 2014-01-07
--------------+----------+-------+-----------

I would love to have a view that looks looks like this:

                             sale_with_cost_of_goods
--------------+----------+-------------+------------+-----------+-----------+----------
 inventory_id | quantity | total_price | timestamp  | cogs_fifo | cogs_lifo | cogs_avg 
--------------+----------+-------------+------------+-----------+-----------+----------
 trinket      |        1 |       $3.00 | 2014-01-04 |     $1.00 |     $2.00 |    $1.50 
 bobble       |        1 |       $3.00 | 2014-01-05 |     $1.00 |     $1.00 |    $1.00 
 trinket      |        2 |       $6.00 | 2014-01-06 |     $3.00 |     $3.00 |    $3.00
 trinket      |        1 |       $3.00 | 2014-01-07 |     $2.00 |     $1.00 |    $1.50
--------------+----------+-------------+------------+-----------+-----------+----------

I can handle the total price and average cost of goods sold, but the FIFO and LIFO queries are kicking me where it hurts.

Is this reasonable or even possible? Any help would be greatly appreciated.

Answer 1

I had a similar problem when a client moved from standard costing to FIFO costing for inventory. My solution was: 1. Add a column to the purchase table for 'remaining qty' 2. Add a column to the sales table for 'COGS' 3. Modify the code in the sales order shipping module to calculate the COGS based on the oldest remaining qty from purchasing (sometimes more than one row) and at the same time reduce the remaining qty accordingly. The query then becomes simple since all the data is sitting in the sales table.