Mysql – Warehouse storage system (FIFO by BBD and date)

database-designMySQLquery

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

Best Answer

Check this solution:

WITH 
cte1 AS ( SELECT *, 
          SUM(CASE WHEN Quantity > 0 
                   THEN Quantity END) OVER (PARTITION BY ItemId 
                                            ORDER BY InventoryDate) incomeQuantity, 
          SUM(Quantity) OVER (PARTITION BY ItemId 
                              ORDER BY InventoryDate) balance
          FROM inventory ),
cte2 AS ( SELECT ItemID, -SUM(Quantity) spent
          FROM inventory
          WHERE Quantity < 0
          GROUP BY ItemId )
SELECT cte1.ItemID, 
       cte1.InventoryID, 
       cte1.InventoryDate, 
       cte1.Quantity,
       cte1.MoneyValuePerUnit,
       CASE WHEN cte1.incomeQuantity - cte2.spent <= cte1.Quantity
            THEN cte1.incomeQuantity - cte2.spent
            WHEN @amount + cte2.spent >= cte1.incomeQuantity
            THEN Quantity
            ELSE GREATEST(@amount + 
                          cte2.spent - 
                          COALESCE(LAG(cte1.incomeQuantity) OVER (PARTITION BY ItemId 
                                                                  ORDER BY InventoryDate), 0), 0)  
            END expense
FROM cte1
JOIN cte2 USING (ItemId)
WHERE cte1.incomeQuantity > cte2.spent
  AND cte1.Quantity > 0
ORDER BY cte1.InventoryDate

where @amount - the amount of good (itemId) which must be sold. The variable is used because only one itemId is present in data, really this must be a joined table order (itemId unique, amount), which contains the amount for each good to be sold in the order.

fiddle - test it carefully in all possible datasets. But please note - the query does not show that the requested quantity is greater than the stock balance. It also does not take into account the value of BBU if it is not NULL, add the appropriate sorting level.

Related Question