Untitled

SELECT 
    COUNT(DISTINCT r.serialnumber) AS wh_qty,
    q.*,
    DATE_ADD(t.cutoff_time,
        INTERVAL - 1 HOUR) AS NeededBy,
    s.shippingoption
FROM
    (SELECT 
        COUNT(DISTINCT invoice) AS orders,
            DATE(p.orderdate) AS orderdate,
            p.invoice,
            p.status,
            o.*
    FROM
        (SELECT 
        CONCAT('s', serialnumber) AS serialnumber,
            product,
            size,
            location,
            bin
    FROM
        inventoryitem
    WHERE
        location = 'STUD-Sellable Photo Studio'
            AND qtyonhand = 1
    LIMIT 100000) o
    JOIN shipment p ON o.product = p.productcode
        AND o.size = p.option1
        AND status IN ('ready' , 'printed', 'printing', 'picked', 'cant find')
    GROUP BY product) q
        LEFT JOIN
    inventoryitem r ON q.product = r.product
        AND q.size = r.size
        AND r.qtyonhand = 1
        AND (r.location LIKE '7-%'
        OR r.location = 'Pack Pending'
        OR r.location = 'Repack'
        OR r.location = 'FWD Returns')
        AND r.location NOT LIKE '7-Q%'
        AND r.location NOT LIKE '7-R%'
        AND r.location NOT LIKE '7-S%'
        JOIN
    orders s ON q.invoice = s.invoicenum
        JOIN
    id.shipping_pickuptime t ON t.shippingoption = s.shippingoption
GROUP BY q.invoice , q.product , q.size
HAVING orders > wh_qty