I have inventory table.
CREATE TABLE inventory ( InventoryID int(11) unsigned PRIMARY KEY AUTO_INCREMENT, InventoryDate datetime, Quantity decimal(15,4), BBD date NULL, ItemID int(11) unsigned NULL, MoneyValuePerUnit decimal(20,4) NULL);
and rows inside
INSERT INTO inventory (InventoryDate, Quantity, BBD, ItemID, MoneyValuePerUnit) VALUES ('2019-12-01 16:23:39', 100, null, 1, 1.23), ('2019-12-02 16:27:11', 200, null, 1, 1.55), ('2019-12-03 16:31:58', 100, null, 1, 1.23), ('2019-12-10 16:23:39', -100, null, 1, 1.23), ('2019-12-11 16:23:39', -137, null, 1, 1.55);
It is about warehouse managment system. Some product (ItemID=1) is taken to warehouse 1,2,3 dec (with different prices) and some quantities left warehouse (10 and 11 dec) with corresponding price.
Warehouse is working FIFO (or, if we have BBD (best before date) then the worst BBD first)
So, my problem is, when I'm creating outgoing order in my system, the corresponding quantity of goods must left the warehouse = I need to insert one+ rows to table
inventory with negative
Quantity (like 2 last rows in this table).
But, I need to calculate what BBD and price will left warehouse. In this example, I have no BBD's at all (just for example I have product without BBD), but I have different purchase price (1.23 and 1.55 per item)
So, when I added first row, I took 100 quantities of item per 1.23/item Second row: +200 quantities but different price 1.55 Third row: +100 quantities but price is also 1.23
Then I received first sales order for 237 items. And I need to "reduce" warehouse sequentially, starting from first item in stock. In this example I see, that I have 100 items with price 1.23, so I insert row N 4, to deduct this amount by this price next I have to deduct 137 items, and I have 200 items by price 1.55, so I insert row n 5 to deduct 137 items by 1.55
Now I have in warehouse 100 items by 1.23, and 63 items by 1.55. But order is very important. Because items per 1.55 (row 2) came first than items by 1.23(row 3), and row 1 is "empty".
And finally, I received second sales order for 100 items. So I have to somehow calculate, what I need to reduce firstly 63 items by price 1.55, and only then 37 items by price 1.23
Question is: How can I calculate it? I have that products left warehouse obey the following rules: worst BBD first. If BBD is the same, then first come left first (by InventoryDate)
SQLFiddle is here: http://sqlfiddle.com/#!9/99cf48/4