Query Date Criteria to use most recent dates

[bhujanga]

I have a supply inventory data table that includes Item Description, Date of Inventory, and Inventory Quantity.
It keeps a running history of inventory of inventory quantities and the date. It is a child table to the Supply table so there can be many records for each supply. Not all supplies get inventories at the same time because it takes several days to complete an inventory and some supplies are inventoried less frequently than others.
I want to be able to see the rate at which supplies are being depleted, so I want to query in such a way to take the most recent quantity, subtract it from the second most recent quantity, and then divide that by the difference in days between the two associated dates.
Since the dates vary from item to item I can't figure out how tell it that I want to use the two most recent records.
So let's say the table looks like this:
Item Desc. Inventory Date Quantity
Widget A ...... 3/1/14 ....... 700
Widget A ...... 4/3/14 ....... 380
Widget A ...... 4/30/14 ...... 210
Widget B ...... 3/6/14 ........ 43
Widget B ...... 5/15/14 ....... 21
Widget C ...... 4/7/14 ...... 1200
Widget C ...... 4/15/14 ...... 980
Widget C ...... 5/1/15 ....... 700

I would want the query to calculate:
Widget A (380-210)/(4/30/14 - 4/3/14)
Widget B (43-21)/(5/15/14 - 3/6/14)
Widget C (980-700)/(5/1/14 - 4/15/14)
(The results are average daily depletion rates)
Is this possible?
Thanks.

 

[MajP]

something like this would give you all the depletion rates and show you the before dates/amounts and after dates/amounts

SELECT 
 CalcInventory.ItemDesc, 
 CalcInventory.OrderDate, 
 CalcInventory.OrderAmount, 
 (Select Max(Previous.OrderDate) from CalcInventory as Previous 
 where 
   CalcInventory.ItemDesc = Previous.ItemDesc and Previous.OrderDate < CalcInventory.OrderDate) AS PrevDate,  
 (Select Top 1 Previous.OrderAmount from CalcInventory as Previous where CalcInventory.ItemDesc = Previous.ItemDesc and Previous.OrderDate < CalcInventory.OrderDate order by previous.orderdate desc) AS PrevAmount, 
 [PrevAmount]-[OrderAmount] AS AmountChange, 
 [OrderDate]-[PrevDate] AS ElapsedDays, 
 [AmountChange]/[ElapsedDays] AS DepletionRate
FROM 
 CalcInventory
WHERE 
 ((((Select Max(Previous.OrderDate) from CalcInventory as Previous where CalcInventory.ItemDesc = Previous.ItemDesc and Previous.OrderDate < CalcInventory.OrderDate)) Is Not Null));

 

[bhujanga]

Thank you. I'll see if I can make it work.

 

[bhujanga]

This is very good, but it is giving me every adjacent pair of dates rather then just the most recent.
Specifically this my actual version of the query:

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;




And this is the last few records of results:
Item InventoryDate UnitsInStock PrevDate PrevAmount AmountChange ElapsedDays DepletionRate
Ziplock Bags (9x12) 3/28/2014 9000 1/28/2014 19000 10000 59 169.491525423729
Ziplock Bags (9x12) 4/28/2014 6000 3/28/2014 9000 3000 31 96.7741935483871
Ziplock Bags (9x12) 5/28/2014 5000 4/28/2014 6000 1000 30 33.3333333333333
Zylatol 3/29/2014 250 1/29/2014 1100 850 59 14.406779661017
Zylatol 4/29/2014 100 3/29/2014 250 150 31 4.83870967741936
Zylatol 5/29/2014 70 4/29/2014 100 30 30 1

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

Thanks.