SAPB1 Query for Inventory Trends by Week

[Former Member]

Hello Experts,

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.

Please help

SELECT

OITW.WhsCode

,OITM.ItemCode

,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

WHERE OITM.ItemCode=OITW.ItemCode

and OITW.WhsCode = '01'

and (OITW.OnHand > 0 or OITW.OnOrder > 0 or OITW.IsCommited > 0)

and OITM.QryGroup1 = 'Y'

and OITM.QryGroup7 = 'Y'

GROUP BY

OITW.WhsCode

, OITM.ItemCode

, OITM.ItemName

, OITM.QryGroup1

, OITM.QryGroup7

, OITM.CardCode

, OITM.AvgPrice

ORDER BY OITM.ItemCode

[Former Member]

Hello @Johan,

This is great. Thank you for your help. In the Where clause i have designated the warehouse code? Should I also insert a where clause into the Subquery to only look at inventory at warehouse 01? thank you for your help!!!

[Johan_H]

Hi Rob,

Please give this a try:

SELECT w.WhsCode
      ,w.ItemCode
      ,i.ItemName AS 'Description'
      ,i.QryGroup1 AS 'AFM'
      ,i.QryGroup7 AS 'CurrentMY'
      ,SUM(w.OnHand) AS 'On Hand Current'
      ,SUM(w.OnOrder) AS 'On Order Current'
      ,SUM(w.IsCommited) AS 'Committed Current'
/* Please note that the following will give you
   the total stock, not just the stock for the
   given warehouse */
      ,(select SUM(InQty-OutQty)
        from OINM
        where DocDate <= DATEADD(day,-7, GETDATE())
          and ItemCode = w.ItemCode) AS 'On Hand 7 Days ago'
      ,(select SUM(InQty-OutQty)
        from OINM
        where DocDate <= DATEADD(day,-14, GETDATE())
          and ItemCode = w.ItemCode) AS 'On Hand 14 Days ago'
      ,(select SUM(InQty-OutQty)
        from OINM
        where DocDate <= DATEADD(day,-21, GETDATE())
          and ItemCode = w.ItemCode) AS 'On Hand 21 Days ago'
      ,(select SUM(InQty-OutQty)
        from OINM
        where DocDate <= DATEADD(day,-28, GETDATE())
          and ItemCode = w.ItemCode) AS 'On Hand 28 Days ago'
      /*, etc */
FROM OITW w
     INNER JOIN OITM i ON w.ItemCode = i.ItemCode
WHERE w.WhsCode = '01'
  and (w.OnHand <> 0 or w.OnOrder <> 0 or w.IsCommited <> 0) /* <- this will effectively
      filter out all entries that do not CURRENTLY have stock 
      or are CURRENTLY committed or have CURRENTLY been ordered.*/
  and i.QryGroup1 = 'Y'
  and i.QryGroup7 = 'Y'
GROUP BY w.WhsCode
        ,w.ItemCode
        ,i.ItemName
        ,i.QryGroup1
        ,i.QryGroup7
ORDER BY ItemCode

If you are not getting any results, it is likely due to the WHERE clause. Easiest way to find out, is to comment out each parameter in turn. Alternatively, you can find an item that you would expect to be on the list, and check its properties against the parameters in the query.

Regards,

Johan