CODEWITHSUNDEEP
sqlpostgresqlaccounting
zakm
zakm

Reputation: 175

Calculating Cost of Goods Sold

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.

Upvotes: 6

Views: 2508

Answers (1)

Asympt0te
Asympt0te

Reputation: 41

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.

Upvotes: 2

Related Questions