CODEWITHSUNDEEP
mysql
Werner
Werner

Reputation: 1847

Calculate costs from purchases with variant prices

Imagine you have a shop and a stock. You are purchasing products for different prices and want to calculate the costs for every sale. The problem is that the purchase-prices are not fix and you might have sales where you are selling items from different purchases with mixed purchase-prices. The costs should be calculated in a kind of "first in first out"-principle: You are selling the oldest items on stock first.

So for example this might happen:

So trying to put this in 2 tables:

table: purchases

purchase | product | amount | purchase price per item|       date |
      1          1        6                     10.00   2018-10-01
      2          1        8                      8.00   2018-11-01
      3          1        4                     12.00   2018-12-01

table: sales

sale | product | amount |      date | costs
   1         1        2   2018-10-04     ??
   2         1        7   2018-11-06     ??
   3         1        6   2018-12-10     ??

The values in sales.costs should be:

Sale 1: 2*10.00 = 20.00
Sale 2: 4*10.00 + 3*8.00 = 64.00
Sale 3: 5*8.00 + 1*12.00 = 52.00

Any ideas if/how it might be possible to calculate this in MySql 5.6 with an UPDATE-statement? I have to admit I haven't even got an idea how to start here or what to try.

Upvotes: 0

Views: 61

Answers (1)

Marcus
Marcus

Reputation: 1930

First of all i add a new Table called stock. Inside this table I store every change in stock. It looks like this:

purchase | sold |       date |
       1 |    2 | 2018-10-04 |
       1 |    4 | 2018-11-06 |
       2 |    3 | 2018-11-06 |
       2 |    5 | 2018-12-10 |
       3 |    1 | 2018-12-10 |

You can fill this table with a TRIGGER if you want or by hand. Now I use this script:

SET @toSell:=5;
SELECT s.stock, p.price, LEAST( s.stock, @toSell ) AS toSell , @toSell * p.price AS totalPrice, @toSell := @toSell - LEAST( s.stock, @toSell ) AS stillNeeded
FROM (
    SELECT p.purchase, SUM( s.sold ) AS  'sold', p.amount, IFNULL( p.amount - SUM( s.sold ) , p.amount ) AS  'stock'
    FROM purchases AS p
    LEFT OUTER JOIN stock AS s ON p.purchase = s.purchase
    GROUP BY p.purchase
    ORDER BY p.date
) AS s
RIGHT OUTER JOIN purchases AS p ON s.purchase = p.purchase
WHERE s.stock >0 AND LEAST( s.stock, @toSell ) > 0;

To calculate the cost for every sale. At the beginning I define a variable containing the amount of items to sell. This Variable will be manipulated in every row. The inner SELECT is a LEFT OUTER JOIN to get the amount of items inside the stock of every purchase ordered by date to use the oldest items first (FIFO).

The Second SELECT use now the stock items and reduce the variable row by row until no more items where needed. You simply have to SUM the totalPrice column to get the costs.

To Test the script i add a new row/purchase 

purchase | product | amount | purchase price per item|       date |
       4 |       1 |     10 |                      5 | 2019-02-04 |

When you run this script with SET @toSell:=2 the result will look this way:

stock | price | toSell | totalPrice | stillNeeded |
    3 |    12 |      2 |         24 |           0 |

And with SET @toSell:=5

stock | price | toSell | totalPrice | stillNeeded |
    3 |    12 |      3 |         60 |           2 |
   10 |     5 |      2 |         10 |           0 |

Hope it helps :)

Upvotes: 1

Related Questions