Untitled

SELECT 
    serial,
    product,
    size,
    location,
    previouslocation,
    lastscandate,
    ifnull(name,agentID) as name,
    division
FROM
    (SELECT 
        CONCAT('s', serialnumber) AS serial,
            product,
            size,
            location,
            previouslocation,
            lastscandate,
            #SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1), ',', - 1) AS username_,
            (SELECT 
                    SUBSTRING_INDEX(username, ':', - 1)
                FROM
                    shard.inventoryitemupdate FORCE INDEX (SERIALNUMBER)
                WHERE
                    serialnumber = t0.serialnumber
                        AND lastscandate >= CURRENT_DATE
                        AND location = 'returns'
                        AND qtyonhand > 0
                        AND username != 'none'
                GROUP BY serialnumber) AS agentID
    FROM
        shard.inventoryitemupdate t0
    WHERE
        location = 'pa returns'
            AND lastscandate >= CURRENT_DATE
            AND previouslocation = 'returns'
    HAVING agentID IS NOT NULL) j1
        left JOIN
    employee j2 ON id = agentID - 1000