Hi, I have in shortcut
- table Products
- table Deliveries with a link to a product and amount of the product we bought in that particular delivery
and some other tables storing how many products were sold, used, etc.
and I would need to have a Stock table with rows grouped by product with information how many of product from that delivery is still in a warehouse. Simply first in first out.
To do that I would need to know how many pieces I have already bought in the time of a particular Delivery.
For example we have 3 deliveries of 5, 10 and 15 pieces and we sold 17 pieces so I would like to have 3 rows of Product, delivered amount, amount left.
In this it would be
XX - 5 - 0
XX - 10 - 0
XX - 15 - 13
There is 0 pieces from the first delivery, 0 pieces form the second delivery and 13 pieces from the last delivery.
Does it make sense? Any idea how to do that? The problem is how to calculate that after fisrt delivery we have bought altogether 5 pieces, after second delivery 15 pieces and after third delivery 30 pieces altogether.
