Re: Largest Inventory Quantity of a Part

If you drop the zero balances it helps:

with t as(SELECT LPROD, LWHS, max(LOPB-LISSU+LADJU+LRCT) as OnHand
-- leave off the location from above as we're trying to
-- find the maximum by product and warehouse
FROM ili
group by lprod, lwhs)
select t.lprod, t.lwhs, lloc, t.OnHand
from t join ili on t.lprod=ili.lprod and t.lwhs=ili.lwhs and
t.OnHand=LOPB-LISSU+LADJU+LRCT
-- find the location that has the same balance as the maximum
-- for the warehouse
where t.OnHand>0 -- skip zero balances
order by lprod, lwhs
Item Whs Location ONHAND
Number
.06POLY48X96 CY CY 14.069
.187 IN 39 39 8.000
*PSH006000 WY WY 427.000
A-73819-1 E4 FI0212 1,000.000
A-73819-1 E4 FK0121 1,000.000
A-73819-1 E4 FK0311 1,000.000
-- note: If these balances were not the same, you'd only see 1 of them.
ACTIVATORD C1 C1 4.000
ACTIVATORD E4 RF1821 6.000
ALPHA1896/15C E4 RB0822 203.000
ALPHA1897C E4 RB0822 39.000
ALPHA1898/15C E4 RI2131 328.000
ALPHA2172 E4 RB1512 137.000

The above is like the two query method. But you're just piping the
results from the one into the next. I forget the technical term -
correlated sub query or some such thing.

Rob Berendt