Problem with Query Max/Mix Values in Warehouses

[Former Member]

Hello,

i wrote query for min and max values in warehouses but i have problem. I dont want items in query results where WhsCode "wyr" have minstock values =<  0.

When i added  t0.MinStock > 0 query didnt sum correctly all magazines.

Select t0.ItemCode [Kod Produktu],t1.ItemName [Nazwa towaru], sum (t0.OnHand) [W magazynie], sum (t0.onorder) [Zamowione], sum (t0.iscommited) [potwierdzone], sum(isnull(t0.MinStock,0)) [Min], sum (isnull(t0.MaxStock,0)) [Max],

sum ((t0.OnHand-isnull(t0.minStock,0))*(-1))[niedobor do min] ,case when sum(isnull(t0.MaxStock,0))=0 then 0 else sum (( t0.OnHand-isnull(t0.MaxStock,0))*(-1)) end [niedobor do max]

from OITW t0

join oitm t1 on t1.ItemCode=t0.ItemCode

where t0.WhsCode IN ('wyr', 'mz1','mz2','mz3','mz4') 

GROUP BY t0.itemcode, t1.ItemName

I'll try show example:

ItemCode X

Magazine WYR  Min Stock 100 OnHand 100

ItemCode X

Magazine MZ1  Min Stock 20 OnHand 100

ItemCode X

Magazine MZ2  Min Stock 0 OnHand 0

I want result:

ItemCode X MinStock120 On Hand 0

But where:

ItemCode Z     Magazine WYR Min Stock 0 On Hand 0

ItemCode Z

Magazine MZ1 Min Stock 0 On Hand 0

i dont want this index in query result.

Any suggestions?

[Former Member]

Hi Krystian,

Try this query, it will show only values > 0 for the agregate of minStock:

Select

t0.ItemCode [Kod Produktu],

t1.ItemName [Nazwa towaru],

sum (t0.OnHand) [W magazynie],

sum (t0.onorder) [Zamowione],

sum (t0.iscommited) [potwierdzone],

sum(isnull(t0.MinStock,0)) [Min],

sum (isnull(t0.MaxStock,0)) [Max],

sum ((t0.OnHand-isnull(t0.minStock,0))*(-1))[niedobor do min] ,

case

  when sum(isnull(t0.MaxStock,0))=0 then 0

  else sum (( t0.OnHand-isnull(t0.MaxStock,0))*(-1))

end [niedobor do max]

from OITW t0

join oitm t1 on t1.ItemCode=t0.ItemCode

where t0.WhsCode IN ('wyr', 'mz1','mz2','mz3','mz4')

GROUP BY t0.itemcode, t1.ItemName

HAVING sum(isnull(t0.MinStock,0)) > 0

hope this helps,

juanpa

[juancarlos_huerta]

Try this query:

Select t0.ItemCode [Kod Produktu],t1.ItemName [Nazwa towaru], sum (t0.OnHand) [W magazynie], sum (t0.onorder) [Zamowione], sum (t0.iscommited) [potwierdzone], sum(isnull(t0.MinStock,0)) [Min], sum (isnull(t0.MaxStock,0)) [Max],

sum ((t0.OnHand-isnull(t0.minStock,0))*(-1))[niedobor do min] ,case when sum(isnull(t0.MaxStock,0))=0 then 0 else sum (( t0.OnHand-isnull(t0.MaxStock,0))*(-1)) end [niedobor do max]

from OITW t0

join oitm t1 on t1.ItemCode=t0.ItemCode

and t0.WhsCode IN ('mz1','mz2','mz3','mz4') or (T0.WhsCode = 'wyr' and T0.MinStock >0)

GROUP BY t0.itemcode, t1.ItemName

[Johan_Hakkesteegt]

Hi Krystian,

Could you please test something ?

Please add this at the end of the query (after GROUP BY t0.itemcode, t1.ItemName😞

HAVING sum (t0.OnHand) > 0 OR sum(isnull(t0.MinStock,0)) > 0

Regards,

Johan