I'm looking for a query which will give me closing inventory weekly for the past 8 weeks. We are trying to see inventory trends and compare to back-order demand. Since we are unable to fulfill orders for product that is not in stock we typically will not process the SO and we lose visibility on the demand. This will help us improve visibility on our inventory trends.
I wrote the following query but its not returning any results when trying to bin the ending inventory 7 days ago.
,OITM.ItemName AS 'Description'
,OITM.QryGroup1 AS 'AFM'
,OITM.QryGroup7 AS 'CurrentMY'
,SUM(OITW.OnHand) AS 'On Hand'
,SUM(OITW.OnOrder) AS 'On Order'
,SUM(OITW.IsCommited) AS 'Committed',
(SELECT SUM(OINM.InQty-OINM.OutQty) 'On Hand' FROM OINM WHERE OINM.DocDate = DATEADD(day,-7, GETDATE()) AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode, OINM.DocDate) AS '7 Days ago'
FROM OITM, OITW
and OITW.WhsCode = '01'
and (OITW.OnHand > 0 or OITW.OnOrder > 0 or OITW.IsCommited > 0)
and OITM.QryGroup1 = 'Y'
and OITM.QryGroup7 = 'Y'
ORDER BY OITM.ItemCode