Thread: SQL Query Help

Greetings All,

I'm looking to add a 'product feed' that will actually pull order details for orders meeting a specific criteria.

The actual criteria I want is as follows :-

( orders in the last 7 days that have an OrderSubtotal greater than 100 AND have more than 5 items in the order )

OR

( orders in the last 7 days of any value that contain products that are in category IDs 123, 124 or 126. )

I've got an xml package working with the following SQL :-

Code:

Select TOP 25 OrderNumber, ShippedOn, FirstName, LastName, Email, Phone, LevelName, OrderSubtotal from Orders with (NOLOCK) where OrderSubtotal > 100 order by OrderNumber desc;

As you can see though, this only pulls the last 25 orders with a subtotal greater than 100.

I'd be grateful for any assistance in meeting the rest of my requirements.


TTFN

BFG

For the first you will have to use a datediff with combination of getDate() to get your date offset and use subquery to get the count of items in the query where the count is > 5.

For the second, you will still use the same date filtering and for the category filtering use a subquery joining the order items's productID to the productcategory table where category is in the list of IDs you have.

*Pseudocode wise ;-)

Thanks Jan,

I don't suppose you'd be willing to un-psuedoify* that for me?

*Obviously, that's not a real word, but you get my meaning :-)


TTFN

BFG

:-) here is for the first one:

SELECT * FROM Orders O WHERE DATEDIFF ( d , O.OrderDate , getDate() ) <= 7 AND O.OrderTotal > 100 AND (SELECT COUNT(*) FROM Orders_ShoppingCart OS WHERE OS.OrderNumber=O.OrderNumber) > 5

Thanks Jan!

I should be able to use that to help me build the second part - I'll post the end result here when finished in case it's useful for anyone else.


TTFN

BFG

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