6

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.

zakm
  • 175
  • 7
  • How to solve this really depends on when the tables are being populated. Are purchase and sale being updated so that each row is added in time order? Are they already populated and the cogs data is after the fact? – gwaigh Sep 22 '14 at 16:05
  • The sale and purchase tables are just appended to, most recent at the end. So if I were to buy another bobble now, it would become the last row of the purchase table. The way I'm picturing the cost of goods calculation is that it would be defined as a view, so the calc would happen on the fly when you select from it. – zakm Sep 22 '14 at 23:07

1 Answers1

2

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.

Asympt0te
  • 41
  • 5
  • I see that post is old. But anyway. So for example on 2014-01-04 sell sell 1 trinket. From `purchase` get price where timestamp is the oldest and `remainingQty` > 0? and update `purchase` set new `remainingQty`. And in `sale` row insert one value (total sum of `COGS`)? Seems interesting. But problem is if with retroactive date record in `purchase`. Then `COGS ` value is incorrect... – Andris May 19 '23 at 04:48