Getting Last

[bhujanga]

This my actual version of a query to get the depletion rate of inventory using a table called "SupplyInventoryHistory" columns Item, InventoryDate and UnitsInStock. The idea is it takes consecutive inventory dates and quantities for each item and computes how much it has changed per day. The only problem is that it's getting every pair of dates and I just ant the most recent pair.

SELECT SupplyInventoryHistory.Item, SupplyInventoryHistory.InventoryDate, SupplyInventoryHistory.UnitsInStock, (Select Max(Previous.InventoryDate) from SupplyInventoryHistory as Previous
where
SupplyInventoryHistory.Item = Previous.Item and Previous.InventoryDate < SupplyInventoryHistory.InventoryDate) AS PrevDate, (Select Top 1 Previous.UnitsInStock from SupplyInventoryHistory as Previous where SupplyInventoryHistory.Item = Previous.Item and Previous.InventoryDate < SupplyInventoryHistory.InventoryDate order by previous.InventoryDate desc) AS PrevAmount, [PrevAmount]-[UnitsInStock] AS AmountChange, [InventoryDate]-[PrevDate] AS ElapsedDays, [AmountChange]/[ElapsedDays] AS DepletionRate
FROM SupplyInventoryHistory
WHERE ((((Select Max(Previous.InventoryDate) from SupplyInventoryHistory as Previous where SupplyInventoryHistory.Item = Previous.Item and Previous.InventoryDate < SupplyInventoryHistory.InventoryDate)) Is Not Null))
ORDER BY SupplyInventoryHistory.Item;


This is the last few records of results:
Item InventoryDate UnitsInStock PrevDate PrevAmount AmountChange ElapsedDays DepletionRate
Ziplock Bags 3/28/2014 9000 1/28/2014 19000 10000 59 169.4
Ziplock Bags 4/28/2014 6000 3/28/2014 9000 3000 31 96.7
Ziplock Bags 5/28/2014 5000 4/28/2014 6000 1000 30 33.3
Zylatol 3/29/2014 250 1/29/2014 1100 850 59 14.4
Zylatol 4/29/2014 100 3/29/2014 250 150 31 4.8
Zylatol 5/29/2014 70 4/29/2014 100 30 30 1

What do I need to do to trim to down to just the last row of each set?

Thanks.

 

[PHV]

Perhaps this ?
WHERE ((((Select Max(Previous.InventoryDate) from SupplyInventoryHistory as Previous where SupplyInventoryHistory.Item = Previous.Item and Previous.InventoryDate < SupplyInventoryHistory.InventoryDate)) Is Not Null))
[!]AND SupplyInventoryHistory.InventoryDate=(SELECT Max(L.InventoryDate) FROM SupplyInventoryHistory L WHERE SupplyInventoryHistory.Item=L.Item)[/!]
ORDER BY SupplyInventoryHistory.Item

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886