[SOLVED] SQL - Dormant Accounts

[Canman2005]

Hi all

 

I am wondering if anyone can help.

 

Basically I have the following tables;

 

`users`

 

`orders`

 

the `users` table contains ID numbers

 

the `orders` table holds the following fields

 

`userid`

`orderdate`

 

The `userid` field relates to the ID numbers in the `users` table

 

The `orderdate` is simply a date stamp such as 2009-01-19 12:22:34

 

At the moment, if I do the following QUERY

 

SELECT * FROM `orders`

 

then I get the list of every order stored, which is fine.

 

What I want to do, is grab a list of `userid` numbers that have not had an entry in the `orders` table between two dats, so basically getting a list of all `userid` numbrs that have not placed an order between say 2008-10-23 and 2008-12-21, this would then give dormant accounts.

 

Can anyone help?

 

Thanks

 

Dave

[xtopolis]

SELECT u.userid, u.name, COALESCE(o.orderdate,'Never')
FROM users u
LEFT JOIN orders o USING(userid)
WHERE o.orderdate 
NOT
BETWEEN '2008-10-23'
AND CURDATE()
OR o.orderdate IS NULL
GROUP BY userid

This will show users that have never ordered as well.  You can replace  CURDATE() with your upper (most recent date), but I put the current date because specifying a range less than today leaves room for them to order between then and the current date (making them active).