Identify oldest inventory

[psalmon]

I am trying to identify how long inventory has been in a specific location. The information that I have is the current inventory level and movements that have put the inventory there. Assuming FIFO I would like figure out the following.

ex: I have 4 movements with various dates AND I have 3 pieces currently on hand, how do identify the date of the last 3 movements (assuming that the one that has been moved out was the first one in)? Of course there is a long list of part #'s, on hand quantities and movements into the location so the logic should work for all.

Sample data

Part # Date Quantity
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
123 07/30/1999 1
456 09/30/1999 1
456 09/01/1999 1


Part # On Hand
123 3
456 1

the Result I would like to see:
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
456 09/30/1999 1

Is there a way to do this?

Thanks
Pat

 

[FoFa]

maybe I am confused, but can't you sort by the part then the date descending?

 

[psalmon]

Yes I can, but there are four records with dates and only 3 in stock. I would to find a way to eliminate the earliest record. This is of course a simplified example. I might have 6 records with the dates and only 1 in stock so I would need to ignore 5 of the records.

Is this clearer?

Thanks,
Pat

 

[FoFa]

Only select records with a quantity then?