Updating Inventory using First In First out(FIFO)

[vik]

I have a database with lot No and quantity as below. I have to use first in first out concept (FIFO) to issue these materials

LOTNO	Quantity
1	2
2	4
3	13

If requested quantity is 1 the i have to decrease quantity of lotNo 1 by 1 if the requested quantity is more than the quantity available in that lot i have to remove from lotNo 1 then 2 till i get the required quanty.

eg if requested quantity is 8, i have to remove 2 from lot 1 4 from lot 2 and remining 4 from lot 3.

I'm using classic asp as programming language

I dont have any Idea on how to do this

 

[BigDady]

Personally, I would use a date-time stamp column to determine the first in, that way you could refill Lot 1 and Lot 2 after this exercise.
and this would work better as a recursive function. I have spent my entire life avoiding them, this is no different so you may rethink things if you are good with recursive functions.

order and binQ are variables. 'order' is the requested quantity and 'binQ' is the amount in the Lot you are using.

Call function and send the binQ.
In func. get the earliest Lot Quantity. Compare binQ < order. If yes subtract binQ from order. return lot number, amount, and new order. The amount for lot1 and lot two will be 'ALL'.
After the return see if order is zero. If no, secure the lot number as ALL and recall the func with new order number.
When returned order is zero use the lot number with the amount to remove from inventory.

The called func on the third call will execute the 'else' part of the compare loop and return the lot amount number as the 'order' amount and zero the 'order' return.

So the calling func looks like this: function(order) and the return looks return(lot number, amount to remove from lot( can be all), new order amount)

I hope this is clear.