Query to display min inventory level by item on selected WH

[Former Member]

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

[former_member212181 · Accepted solution]

Hi Olga,

Please try below query.

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

[Former Member · Accepted solution]

Hi Balan,

Once again thank you so much.

It works !

Thanks

[isaac_kalii]

Hi Olga,

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]