Untitled

SELECT 
    c.label, d.*
FROM
    (SELECT 
    MAX(reasoncode) reasoncode,
    MAX(createdate) removedate,
    CONCAT('s', t2.serialnumber) serial,
    product,
    size,
    location,
    previouslocation,
    SUBSTRING_INDEX(MAX(source), 'Manual Adjust by ', - 1) source,
    SUBSTRING_INDEX(notes, ']', 1) notes
FROM
    (SELECT 
        id AS decrementid, reasoncode, source
    FROM
        inventoryupdate
    WHERE
        updateday >= CURRENT_DATE) t1
        JOIN
    inventoryupdate_extend t2 ON inventoryupdateid = decrementid
        JOIN
    inventoryitem t3 ON t2.serialnumber = t3.serialnumber
WHERE
    createdate >= CURRENT_DATE
        AND notes LIKE '[Manual decrement%'
        AND removedate >= CURRENT_DATE
GROUP BY serial) d
        JOIN
    (SELECT 
        code, label
    FROM
        inventoryupdatereasoncode
    GROUP BY code) c ON d.reasoncode = c.code
GROUP BY serial
ORDER BY removedate DESC;

SELECT 
    c.label, d.*
FROM
    (SELECT 
        MAX(reasoncode) reasoncode,
            MAX(createdate) AS incrementdate,
            CONCAT('s', t2.serialnumber) serial,
            product,
            size,
            location,
            previouslocation,
            SUBSTRING_INDEX(MAX(source), 'Manual Adjust by ', - 1) source,
            SUBSTRING_INDEX(notes, ']', 1) notes
    FROM
        (SELECT 
        id AS decrementid, reasoncode, source
    FROM
        inventoryupdate
    WHERE
        updateday >= CURRENT_DATE) t1
    JOIN inventoryupdate_extend t2 ON inventoryupdateid = decrementid
    JOIN inventoryitem t3 ON t2.serialnumber = t3.serialnumber
    WHERE
        createdate >= CURRENT_DATE
            AND notes LIKE '[Manual Increment%'
            AND createdate >= CURRENT_DATE
    GROUP BY serial) d
        JOIN (select code, label from inventoryupdatereasoncode group by code) c  on d.reasoncode = c.code
group by serial
order by incrementdate desc;