Corajuda

select cutoff,invoicetype,shippingoption,M.productcode, M.option1,M.serialnumber,pullorleave,L.invoice, tote
from
(SELECT
    T.cutoff_time AS CutOff,
    CASE
        WHEN ordercount = 1 THEN 'single'
        WHEN ordercount = 2 THEN 'two_item'
        WHEN ordercount >= 3 THEN 'multi'
    END AS InvoiceType,
    K.*
FROM
    (SELECT
        O.shippingoption,
            CONCAT('s', GROUP_CONCAT(DISTINCT I.serialnumber)) AS Serials,
            CASE
                WHEN O.shippingoption IN ('3-6 Day Global Express' , 'Employee Shipping', 'Will Call', '2nd Day Air', 'UPS Worldwide Saver', 'Standard RRD', 'Fedex Ground') THEN 'LEAVE'
                WHEN O.shippingoption IN ('Easy Express' , 'Easy Express DDP', 'Fedex International Priority', 'Next Day Air', 'Priority Mail International', 'Fedex Same Day', 'Next Day Air Saver', 'DHL Mail') THEN 'PULL'
                ELSE NULL
            END AS PullOrLeave,
            S.invoice,
            COUNT(S.invoice) AS ordercount,
            I.bin AS Tote
    FROM
        (SELECT
        invoice,
            status,
            REPLACE(serialnumber, 's', '') serialnumber,
            productcode,
            option1
    FROM
        shipment
    WHERE
        status IN ('Picked' , 'Printing', 'Printed')
            AND ordertype = 20) S
    JOIN orders O ON O.invoicenum = S.invoice
    JOIN inventoryitem I ON I.serialnumber = S.serialnumber
        AND bin LIKE 'T%'
        AND bin != ''
        AND bin NOT LIKE 'Tp%'
    GROUP BY S.invoice
    ORDER BY shippingoption , bin DESC) K
        JOIN
    id.shipping_pickuptime T ON T.shippingoption = K.shippingoption) L
join shipment M
on L.invoice= M.invoice
and printbatch >= current_date
group by serialnumber