Problem with Query Max/Mix Values in Warehouses

33 Views

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?