Untitled
unknown
mysql
2 years ago
4.8 kB
4
Indexable
select date(max(lastscandate)) as moved_date, substring_index(group_concat(distinct substring_index(substring_index(locationhistory, ']', if(substring_index(substring_index(locationhistory, ']', 1), ',', - 1) in ('none' , ''), 2, 1)), ',', - 1) order by lastscandate desc), ',', 1) as username, t1.product, t1.size, (select siteflag from product where code = t1.product) as siteflag, group_concat(distinct t1.status) as status, count(distinct if(qtyonhand, serialnumber, null)) as moved, count(distinct if(qtyonhand, null, serialnumber)) as removed, (select sum(if((location like '7-_%' or location = 'inventory') and location not like '3-666Z-_%' and qtyonhand = 1 and if(defective, find_in_set(serialnumber, defective_serials), 1) and serialnumber not in (select replace(serialnumber, 's', '') from helditem where productcode = t1.product and option1 = t1.size and status in ('purchased' , 'ready') and expirationdate >= current_date and find_in_set(email, emails) = 0), 1, 0)) from inventoryitem where product = t1.product and size = t1.size group by product , size) as shelf, holds, orders, (select t2.location from warehouselocation where unsellable = 1 and location not in ('order packing' , 'returns', 'receiving') and if(likemodifier = 1, left(t2.location, length(substring_index(location, '%', 1))) = substring_index(location, '%', 1), t2.location = location) group by location limit 1) as moved_location, sellableqty from (select least(ifnull(orderdate, creationdate), ifnull(creationdate, orderdate)) as logdate, product, size, concat_ws(',', j2.status, j3.status) as status, concat_ws(',', j2.emails, j3.emails) as emails, ifnull(orders, 0) as orders, ifnull(holds, 0) as holds, defective_serials, sellableqty from (select product, option1 as size, sellableqty from inventory force index (last_update) where last_update >= date_sub(current_date, interval 45 day) and sellableqty < 0 group by product , size limit 1000000000) j1 left join (select productcode, option1, group_concat(distinct status) as status, count(shipmentid) as orders, min(orderdate) as orderdate, group_concat(distinct useremail) as emails, group_concat(distinct replace(serialnumber, 's', '')) as defective_serials from shipment where orderdate >= date_sub(current_date(), interval 45 day) and status in ('printed' , 'printing', 'ready', 'cant find', 'verify', 'on hold') and extrastatus != 'on hold' group by productcode , option1 limit 100000) j2 ON j1.product = j2.productcode and j1.size = j2.option1 left join (select productcode, option1, group_concat(distinct if(status = 'Ready', 'ready', 'purchased')) as status, count(id) as holds, min(creationdate) as creationdate, group_concat(distinct email) as emails from helditem where status in ('ready' , 'purchased') and expirationdate >= current_date group by productcode , option1 limit 100000) j3 ON j1.product = j3.productcode and j1.size = j3.option1 group by product , size having logdate limit 100000) t1 join inventoryitem t2 ON t1.product = t2.product and t1.size = t2.size where lastscandate > logdate group by t1.product , t2.size , location having ((holds + orders) - shelf) > 0 and moved_location is not null order by moved_date , username
Editor is loading...