Untitled

SELECT 
    CONCAT('s', i.serialnumber) serial,
    p.siteflag,
    i.product,
    i.size,
    i.location,
    s.location AS prevloc,
    i.lastscandate,
    e.name
FROM
    (SELECT 
        serialnumber,
            Product,
            Size,
            Bin,
            Location,
            Previousbin AS PrevBin,
            PreviousLocation AS PrevLoc,
            Lastscandate,
            Username,
            SUBSTRING(username, 9, 10) - 1000 AS empid
    FROM
        shard.inventoryitemupdate
    WHERE
        username = 'agentId:158876'
            AND lastscandate >= '2023-04-05 09:42:00'
            AND lastscandate <= '2023-04-05 10:42:00'
            AND qtyonhand = 1
    ORDER BY lastscandate ASC) s
        JOIN
    employee e ON e.id = s.empid
        JOIN
    inventoryitem i ON i.serialnumber = s.serialnumber
        JOIN
    product p ON p.code = i.product
WHERE
    i.location = 'returns'
        AND i.bin NOT LIKE 'b________%'