Untitled

SELECT 
    CONCAT('s', serialnumber) AS Serial,
    stylecode,
    Product,
    Size,
    Bin,
    Location,
    Previousbin AS PrevBin,
    PreviousLocation AS PrevLoc,
    Lastscandate,
    CASE
        WHEN lastscandate > '2024-12-03 00:00:00' THEN 'Confirmed'
        WHEN lastscandate < '2024-12-03 00:00:00' THEN 'Pending'
    END AS 'InShelf',
    modifiedBy
FROM
    alliance.allianceinventoryitem
WHERE
    location LIKE '7-%' AND qtyonhand = 1
ORDER BY location DESC;

SELECT 
    COUNT(serialnumber) serials,
    location,
    lastscandate,
    CASE
        WHEN lastscandate > '2024-12-03 00:00:00' THEN 'Confirmed'
        WHEN lastscandate < '2024-12-03 00:00:00' THEN 'Pending'
    END AS 'InShelf',
    modifiedBy
FROM
    alliance.allianceinventoryitem
WHERE
    location LIKE '7-%' AND qtyonhand = 1
    group by InShelf asc
ORDER BY location DESC