slow moving items

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[former_member186095]

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,

[former_member583013]

Henry,

Please look at Inventory > Inventory Reports > Inactive Items

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