Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    May 26, 2015 at 09:06 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 26, 2015 at 08:37 AM

    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]

    Add comment
    10|10000 characters needed characters exceeded