INV Rebalance

select j1.*,
case when m_units is null
or (m_units < b_units and m_units <5) then 'pull' else 'keep' end as 'imb_status'
from
(select lastscandate,product, size, count(serialnumber) b_units,bin, location
from inventoryitem
where qtyonhand=1 and location like '9-%'
group by product, size
limit 100000) j1
left join
(select product, size, count(serialnumber) m_units,bin, group_concat(distinct location) m_locations
from inventoryitem
where qtyonhand=1 and location like '7-%'
group by product, size
limit 100000000) j2
on j1.product =j2.product and j1.size= j2.size
having imb_status = 'pull'
order by location desc
limit 100000