Untitled

 avatar
unknown
mysql
2 years ago
1.1 kB
13
Indexable
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
Editor is loading...