Untitled

 SELECT 
    COUNT(DISTINCT serialnumber) AS units,
    COUNT(DISTINCT bin) AS bins,
    username,
    MAX(DATE(lastscandate)) MonthOf,
    (SELECT 
            location
        FROM
            shard.inventoryitemupdate
        WHERE
            username = t1.username
                AND lastscandate >= TIMESTAMPADD(SECOND, 1, CURRENT_DATE)
        ORDER BY lastscandate DESC
        LIMIT 1) AS location
FROM
    shard.inventoryitemupdate t1
WHERE
    lastscandate >= '2023-07-01 00:00:00'
        AND lastscandate <= '2023-07-31 23:59:59'
        AND username IN ('Maria Carrola 5636' , 'Tania M Cordero 7992',
        'Simona Estrada 6558',
        'Maria Guadalupe Guzman Sotelo 7811',
        'Andrea Montijo 6417',
        'Josefina Ramirez Echaverria 6664',
        'Aileen Bahena 9039')
        AND qtyonhand = 1
GROUP BY username
ORDER BY units DESC