I am working on a project where I have to find one or several empty compartments in my shelving to store the quantity of one SKU.
I need to loop update the rows where QTY is equal to 0 until I complete the whole quantity and I don’t know how to write this looping function in SQL.
Suppose that I am receiving an order with a quantity of 100 of “ABCD” items to store.
Assuming that my SKU “ABCD” represents a volume of 5 compartments (each compartment can only store 20 tiems); I will need to loop trough my entire shelving compartment table to find 5 empty compartments and assign in the SKUnb column the “ABCD” reference and place 20 PCS in the “QTY” column until I complete the 100 PCS. At the same time I flag the “IsInUse” column with a yes.
My Compart table
ShelveID ShelvePosition SKUnb Qty isInUse
1 A001 XYZ 20 yes
2 A002 XYZ 20 yes
3 A003 null 0 no
…. …. …… …… ……
100 X001 null 0 no
101 X002 null 0 no
If for instance the quantity is 110 there will be 5 Shelve position filled with 20 and one with 10. Total 6 empty rows updated.
When I need to retrieve some quantity I will inverse the process and flag the the IsInUse with “no” if the compartment is empty.
Thanks for helping.