WITH recent_cancels AS (
SELECT p.PIN, GROUP_CONCAT(o.OrderID) AS Cancelled_Orders, SUM(ABS(od.QtyOnPackingSlip)) Cancelled_Qty
FROM custopayted.orders o
JOIN custopayted.orderdetails od ON od.OrderID = o.OrderID
JOIN products.pins p ON p.ProductCode = od.ProductCode
WHERE (od.ProductNote LIKE '%(C)%' AND od.QtyOnPackingSlip < 0)
AND o.OrderDate >= CURDATE() - INTERVAL 1 MONTH
GROUP BY p.PIN
)

SELECT sa.*, rc.Cancelled_orders, rc.Cancelled_Qty
FROM products.stock_archive sa
LEFT JOIN recent_cancels rc ON rc.PIN = sa.PIN
WHERE TimeReceived >= CURDATE();