Untitled

SELECT 
    CONCAT('s', i.serialnumber) serial,
    i.product,
    i.size,
    i.bin AS CurrBin,
    s.bin AS OriginalBin,
    i.location AS CurrLoc,
    s.location AS OrigignalLoc,
    i.lastscandate,
    SUBSTRING_INDEX(SUBSTRING_INDEX(i.locationhistory, ']', 1),
            ',',
            - 1) LastUser,
    s.username AS OriginalUser
FROM
    (SELECT 
        serialnumber,
            product,
            size,
            bin,
            location,
            lastscandate,
            SUBSTRING(username, 1, 24) username
    FROM
        shard.inventoryitemupdate
    WHERE
        username = 'jennifer blanco Aguilera 8229'
            AND lastscandate >= '2023-10-01 00:00:00'
            AND lastscandate <= '2023-11-10 00:00:00') s
        JOIN
    inventoryitem i ON s.serialnumber = i.serialnumber
WHERE
    s.location = i.location
        AND qtyonhand = 1
HAVING LastUser = OriginalUser