SET @remaining_stock := 0;
SET @last_productcode := '';

SELECT 
    orderid, 
    orderdetailid, 
    order_entry_system, 
    productnote, 
    orderstatus, 
    quantity, 
    orderdate, 
    productcode, 
    productname, 
    sku, 
    mfg_sku, 
    REL_STOCK, 
    REL_NAME,
    IF(@last_productcode = productcode, @remaining_stock := @remaining_stock - quantity, @remaining_stock := REL_STOCK - quantity) AS stock_after_order,
    @last_productcode := productcode
FROM (
    SELECT 
        o.orderid, 
        od.orderdetailid, 
        o.order_entry_system, 
        od.productnote, 
        o.orderstatus, 
        od.quantity, 
        o.orderdate, 
        od.productcode, 
        od.productname, 
        rel.sku, 
        rel.mfg_sku, 
        rel.stock as REL_STOCK, 
        rel.description as REL_NAME
    FROM custopayted.orders o
    JOIN custopayted.orderdetails od ON o.orderid = od.orderid
    JOIN products.client_map cm ON od.productcode = cm.productcode and cm.client = 'Volusion'
    JOIN products.pins p ON cm.PIN = p.PIN and p.vertical = 'appliance'
    JOIN products._REL rel ON rel.sku = SUBSTRING(od.productcode, 5)
    WHERE 
        (
            (od.productnote LIKE '%SPC%')
            OR
            (od.productnote LIKE '%ETA%')
            OR
            (od.productnote LIKE '%NOO%')
        )
        AND od.productnote NOT LIKE '%STOCK%'
        AND od.productnote NOT LIKE '%CANCELLED%'
        AND od.productnote NOT LIKE '%|%'
        AND o.orderstatus LIKE 'Processing'
		AND rel.stock > 0
        AND od.qtyonhold > 0
        AND od.quantity > 0
        AND o.orderdate >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    ORDER BY od.productcode, o.orderdate ASC
) AS sorted_orders
HAVING stock_after_order >= 0;