Untitled

SELECT 
    releasedate, date(orderdate) orderdate,
    (SELECT 
            CASE
                    WHEN COUNT(*) = 1 THEN 'single'
                    WHEN COUNT(*) = 2 THEN '2-item'
                    WHEN COUNT(*) = 3 THEN '3-item'
                    WHEN COUNT(*) > 3 THEN 'multi'
                END
        FROM
            shipment
        WHERE
            invoice = t2.invoice
                AND status IN ('shipped', 'printing' , 'picked', 'printed', 'picksorted')
                AND productcode != giftproductcode
        GROUP BY invoice) AS 'order type',     
    invoice, 
    case when giftproductcode = 'GWRA-UA8' then 'Wrap UA8'
    when giftproductcode = 'GWRA-UA1' then 'Giftwrap'
    else 'Gift Certificate' end as 'gift type',
    REPLACE(serialnumber, 's', '') AS serial,
    productcode,
    option1,
    status,
(SELECT 
                shippingOption
            FROM
                orders
            WHERE
                transactionid = t2.transactionid
            LIMIT 1) as shippingOption,
    IF(serialnumber LIKE 's%',
        (SELECT 
                IF(bin != '',
                        CONCAT(location, ' (', bin, ')'),
                        location)
            FROM
                inventoryitem FORCE INDEX (SERIALNUMBER)
            WHERE
                serial = serialnumber),
        '') AS current_location
FROM
    (SELECT 
        printbatch AS releasedate, invoice AS invoicenum, productcode as giftproductcode
    FROM
        shipment
    WHERE
        (productcode in ('GWRA-UA1','GWRA-UA8')
            OR productcode LIKE 'GIFT-%')
            AND status IN ('shipped', 'printing' , 'picked', 'printed', 'picksorted')
            AND ordertype != 20) t1
        JOIN
    shipment t2 ON t1.invoicenum = t2.invoice
WHERE
     status IN ('shipped', 'printing' , 'picked', 'printed', 'picksorted')
        AND productcode != giftproductcode
        AND orderdate >= '2024-04-13 00:00:00'
ORDER BY invoice