Search
Search

Query to display min inventory level by item on selected WH

Hi,

I had another request on the min inventory level by item.

Let assume inventory level is mange by item.

There are total of 10 WH.

If the total sum on WH 001,003,004 onhand are more than OITM.Minlevel, then it should display the result.

Can this be possible done through query?

Below query didn't work for me, it only compare the onhand qty in WH 004 with OITM.Minlevel

SELECT T0.[ItemCode], T0.[FrgnName], SUM(T1.[OnHand]), T0.[OnHand], T0.[MinLevel]  FROM OITM T0  INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T1.[WhsCode] IN ('001', '003', '004') AND T0.[MinLevel]>T1.[OnHand] GROUP BY  T0.[ItemCode], T0.[FrgnName],T1.[OnHand], T0.[MinLevel] , T0.[OnHand]

Thanks

Unnikrishnan Balanreplied

Hi Olga,

Select A.ItemCode, A.FrgnName, A.OnHand, A.MinLevel

FROM

(

SELECT T0.ItemCode,SUM(T1.OnHand)[OnHand],Max(T0.MinLevel)[MinLevel],Max(T0.FrgnName)[FrgnName]

FROM OITM T0

INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.InvntItem = 'Y' and T1.[WhsCode] IN ('001', '003', '004')

GROUP BY  T0.[ItemCode]

) A

Where A.MinLevel > A.OnHand

Thanks

Unnikrishnan

0 View this answer in context