Untitled

SELECT 
    CONCAT('s', i.serialnumber) serial,
    p.siteflag,
    group_concat(i.product) products,
    i.size,
    i.location,
    s.location AS prevloc, 
    s.date,
    i.lastscandate,
    (SELECT 
    GROUP_CONCAT(distinct bin) binstoaudit
FROM
    shard.inventoryitemupdate
WHERE
    username = 'Hevi V. Perez Valadez'
        AND lastscandate >= '2023-04-11 07:38:00'
        AND lastscandate <= '2023-04-11 09:38:00'
        AND qtyonhand = 1)binstoaudit,
    e.name
FROM
    (SELECT 
        serialnumber,
            Product,
            Size,
            Bin,
            Location,
            Previousbin AS PrevBin,
            PreviousLocation AS PrevLoc,
            DATE(Lastscandate) date,
            Lastscandate,
            Username,
            SUBSTRING(username, 9, 10) - 1000 AS empid
    FROM
        shard.inventoryitemupdate
    WHERE
        username = 'agentId:4493'
            AND lastscandate >= '2023-04-11 07:38:00'
            AND lastscandate <= '2023-04-11 08:38: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________%'