Skip to Content
avatar image
Former Member

slow moving items

hi pple,

wonna come up with a query in SBO that displays a list of items that have sold less than for instance 5 pieces for a certain duration, say six months.

Any ideas will be highly appreciated. Thanks.

Regards,

Henry

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 04, 2008 at 01:24 PM

    hi pple,

    thank you for your responses. The query below gives you an idea of the code am trying to polish up inorder to achieve my goal:

    SELECT DISTINCT T0.ItemCode, T0.ItemName, T0.OnHand FROM OITM T0 where T0.OnHand>'0' and T0.ItemCode NOT IN(SELECT T4.ItemCode FROM OITM T4 INNER JOIN INV1 T5 ON T4.ItemCode = T5.ItemCode INNER JOIN OINV T6 ON T5.DocEntry = T6.DocEntry INNER JOIN OCRD T7 ON T4.CardCode = T7.CardCode WHERE T6.DocDate <=[%0] and T4.CreateDate<=T6.DocDate and t4.OnHand>'0')

    Anyone with an idea of how best to improve the above code?

    Regards,

    Henry

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Try this:

      SELECT T0.ItemCode, T0.Dscription, T2.Onhand, SUM(T0.Quantity) 
      'QTY Sold' 
      FROM INV1 T0
      INNER JOIN OINV T1 ON T1.DocEntry = T0.DocEntry
      INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode and 
      T2.OnHand > 0
      WHERE (T1.DocDate BETWEEN '[%0]' AND '[%1]' )
      GROUP BY T0.ItemCode, T0.Dscription, T2.Onhand
      HAVING SUM(T0.Quantity) <= '[%2]'
      

      Thanks,

      Gordon

  • avatar image
    Former Member
    Sep 03, 2008 at 01:31 PM

    Henry,

    Please look at Inventory > Inventory Reports > Inactive Items

    This report might not have the provision to specify minimum quantity but should still work

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 03, 2008 at 02:01 PM

    Hi,

    Instead of using inactive item report, it seems the combination of table rdr1 and oitm. you could also include oinm and oitw or oitw only.

    Made the condition the rdr1.quantity < 5 and the duration is optional. it could be decided by user or not.

    you could try and paste here the query you have made and all of us here will help you to correct it if the result is not suitable for you

    Rgds,

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 03, 2008 at 07:00 PM

    Hi Henry,

    Try this one:

    SELECT T0.ItemCode, T0.Dscription, SUM(T0.Quantity) 'QTY' 
    FROM INV1 T0
    INNER JOIN OINV T1 ON T1.DocEntry = T0.DocEntry
    WHERE (T1.DocDate BETWEEN '[%0]' AND '[%1]' ) AND 
    T1.DocType = 'I'
    GROUP BY T0.ItemCode, T0.Dscription
    HAVING SUM(T0.Quantity) <= '[%2]'
    

    It allows you select a certain duration and a certain quantity as you wish.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded