Age of Inventory

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I need to make a query, that takes a part number, lists
all of the reciepts against it, and the date
received,looks at the quantity on hand and tells me the
age (todays date less the received date) of the inventory
on hand.
I have the Age calculation, I cannot seem to figure out
how to 1. determine the age of a partial receipt and
elimiate all other records once the quantity on hand is
met.
For expample.. Part #1 has 59 on hand, I received in 1 on
2/1/04 and 250 on 1/1/04 and 300 on 12/31/04. How can I
make a query spit ouot 1 on 2/1/04 and 58 on 1/1/04 and
not come up with the 12/31/04 record.

Any help will be appreciated.
 
Hi,




Assuming the Inventory table is like

PartID, Qty, DateStamp


with qty>0 for a reception, and qty<0 for an amount getting out.


SELECT z.PartID, Min(z.DateStamp) AS MinOfDateStamp
FROM (SELECT a.PartID, a.DateStamp
FROM Inventory As a INNER JOIN
(SELECT * FROM Inventory WHERE Qty>=0) As b
ON a.DateStamp >= b.DateStamp
GROUP BY a.PartID, a.DateStamp
HAVING SUM(b.qty) >
-(SELECT SUM(Qty) FROM Inventory WHERE Qty<0)
) AS z
GROUP BY z.PartID;



should return, for each PartID, the oldest reception date for some quantity
still in inventory (if material exit accordingly to first in, first out).


The inner most query:

SELECT * FROM Inventory WHERE Qty>=0) As b
ON a.DateStamp >= b.DateStamp
GROUP BY a.PartID, a.DateStamp
HAVING SUM(b.qty) >
-(SELECT SUM(Qty) FROM Inventory WHERE Qty<0)


does most of the job. Basically, it does a cumulative (running) sum of
positive values of qty

SELECT * FROM Inventory WHERE Qty>=0) As b
ON a.DateStamp >= b.DateStamp
GROUP BY a.PartID, a.DateStamp


but only keep those sums that exceed the total value of what has already
left the floor, as of today:

HAVING SUM(b.qty) >
-(SELECT SUM(Qty) FROM Inventory WHERE Qty<0)


It is then a matter to find the minimum date left in that result, for each
PartID, which is done by the outmost query.

I got 2004-01-01 with the data:

Inventory
PartID Qty DateStamp
1 59 2000-01-01
1 1 2003-12-01
1 250 2004-01-01
1 -45 2004-06-06
1 300 2004-12-31
1 -56 2005-01-01



and indeed, having spend 101 quantity of PartID =1, that means there are
still some items dating back as far as 2004-01-01 (ie, every thing we got at
2000-01-01 and at 2003-12-01 should now be out).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top