OK, so here's what I ended up with :-
Code:
SELECT OrderNumber, CONVERT(VARCHAR(8), ShippedOn, 3) AS displaydate, FirstName, LastName, Email, Phone, LevelName, OrderSubtotal FROM Orders O WHERE
(
DATEDIFF ( d , O.ShippedOn , getDate() ) <= 7
AND O.OrderTotal > 100
AND (SELECT SUM(Quantity) FROM Orders_ShoppingCart OS WHERE OS.OrderNumber=O.OrderNumber) > 5
)
OR
(
DATEDIFF ( d , O.ShippedOn , getDate() ) <= 7
AND O.OrderNumber IN (
SELECT OrderNumber FROM Orders_ShoppingCart WHERE ProductID IN (
SELECT ProductID FROM ProductCategory WHERE CategoryID IN (321,654,987)
)))
order by OrderNumber desc;
There's probably a better or more efficient way to do it than nested sub-queries, but this seems to work.
I changed the criteria to be based on order shipped date instead of order date & I display that in the UK style rather than the native SQL style.
Thanks again Jan for your help.
TTFN
BFG