-- SELECT orders that are all received but missing a tracking number
SELECT o.orderid, o.orderdate, o.orderstatus, o.Order_Entry_System
FROM custopayted.orders o
JOIN custopayted.orderdetails od ON od.orderid = o.orderid
LEFT JOIN trackingnumbers t ON t.orderid = o.orderid

WHERE o.orderstatus NOT IN('Shipped', 'Cancelled', 'Partially Returned', 'Pending', 'Returned')
AND od.productcode NOT LIKE 'DSC-%'
AND t.orderid IS NULL

GROUP BY o.orderid

HAVING SUM(CASE WHEN od.qtyonpackingslip = 0 THEN 1 ELSE 0 END) = COUNT(od.orderdetailid)

ORDER BY (CASE WHEN o.Order_Entry_System = 'Marketplace' THEN 0 ELSE 1 END), o.orderdate
;