Untitled

SELECT 
    ai.serialnumber,
    ai.product,
    ai.stylecode,
    ai.location AS ROBLoc,
    r.location AS RVLVLoc,
    ai.bin AS ROBBin,
    r.bin AS RVLVBin,
    ai.lastscandate,
    ai.qtyonhand AS ROBQty,
    r.qtyonhand AS RVLVQty,
    ai.modifiedby,
    CASE
        WHEN NOTES LIKE '%LPA Alliance Transfer%' THEN 'Transferred'
        WHEN NOTES NOT LIKE '%LPA Alliance Transfer%' THEN 'Untransferred'
    END AS TransferStatus,
    r.notes
FROM
    (SELECT 
        serialnumber,
            product,
            stylecode,
            location,
            bin,
            size,
            orderhistory,
            lastscandate,
            removedate,
            modifieddate,
            modifiedby,
            qtyonhand
    FROM
        alliance.allianceinventoryitem
    WHERE
        location = 'cantfind;pending'
            AND bin = 'decrement'
            AND removedate >= '2023-09-25 00:00:00'
            AND product NOT LIKE 'GRLR%'
    GROUP BY serialnumber) AS ai
        JOIN
    (SELECT 
        *
    FROM
        inventoryitem) AS r ON r.serialnumber = ai.serialnumber