Find payments before a specific date

tsiger · #1 (**permalink**) 09-20-07, 03:39

I need some help with a query i am trying to build. I have a table which holds annual member subscriptions. The table is like this

id | memberID | year | amount

and a typical select will return data like that:

1 | 001 | 1999 | 50
2 | 001 | 2000 | 50
3 | 001 | 2001 | 50
4 | 002 | 2000 | 50
5 | 002 | 2001 | 50
6 | 003 | 2000 | 50
7 | 003 | 2001 | 50

what i want to get is all the members who haven't paid after 2002.
Any ideas?

aschk · #2 (**permalink**) 09-20-07, 04:11

I think something like the following should work:

Code:

SELECT memberID,MAX(year) 
FROM <tablename> 
GROUP BY memberID HAVING MAX(year) < 2003

Edit : You don't need the MAX(year) in the select clause part of the statement.

tsiger · #3 (**permalink**) 09-20-07, 04:33

yes this seems to work just fine

thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech