SQL newbie, need help.

[JON10884]

Hello people, I want to run a query for customers whose last order was placed before the beginning of this month. Since there is no LastOrder field, and I can only use OrderDate, how do you write a statement that would take only the latest OrderDate that is less than last month. I really only know the basic stuff, I'm guessing I might need to write an if statement or a loop. Thanks in advance.

[Colin Angus Mackay]

JON10884 wrote: I'm guessing I might need to write an if statement or a loop. Writing loops in SQL is very inefficient, you should only consider this as a last resort. Database systems are fine tuned to work on sets of data so by extracting the set into individual rows and operating on the individual rows is very inefficient. It would be helpful if you could post the relevant tables and columns

---

Do you want to know more? WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums

[Jon Hulatt]

doesn't need a loop. You could use the horrible distinct clause select * from ( select distinct(customerid) from orders where orderdate < '2004-12-01 00:00' ) j left join ( select distinct(customerid) from orders where orderdate >= '2004-12-01 00:00' ) k on k.customerid=j.customerid where k.customerid is null

[Mike Dimmick]

SELECT Customers.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID
HAVING MAX(Orders.OrderDate) < '20041201'

Assuming you keep your orders in an Orders table, and your customers in a Customers table, of course! Stability. What an interesting concept. -- Chris Maunder

[JON10884]

You guys are the best! thanks a lot! But one more question, I'm using Mike's algorithm, how do you select more fields to display?

[WoutL]

As long as you want to select more fields from the customers table, you should include the fields in both the select and group by clause. Like: SELECT Customers.CustomerID**, Customers.Name** FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID**, Customers.Name** HAVING MAX(Orders.OrderDate) < '20041201' Wout Louwers

[JON10884]

I did that but I want the date to display and if you group by date it shows the duplicates.

[WoutL]

I 'me not sure if this will work, but you could try: SELECT Customers.CustomerID, **MAX(Orders.OrderDate)** FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID HAVING MAX(Orders.OrderDate) < '20041201' Wout Louwers

[JON10884]

You the man! Thanks a ton!