Hi there. My goal is to retrieve a set of user_id's who are visible in my system, have a positive balance in the "transactions" table (like a checkbook register, basically), and whom have NOT made a credit in the "transactions" table within the past 10 months. I'd like to warn them because their funds are going to expire after 1 year. Site users need to maintain a positive balance in order to continue to interact with the system.
So to be clear... I'm trying to come up with a list of user id's who:
A.) are active / visible in the system ( users.visible = 1 )
B.) have a positive balance ( sum(transactions.amount) > 0 )
C.) whose last "credit" transaction was inserted more than 10 months ago ( transactions.tx_type = 1 & transactions.date_created ? )
I've got A and B in the bag... I just can't figure out how to further filter for C. The following query gives me a list of visible users with a positive account balance.
user_id in (select id from users where visible = 1)
sum(amount) > 0
Can anyone provide hints for how I can take this further and only find users whose MOST RECENT credit transaction (i.e. transactions.tx_type = 1) date was over 10 months ago? I presume it has something to do with including MAX(transactions.date_created) <= dateadd(mmmm, 10, getdate()) or some such thing... but I can't nail it.
The schema for the transactions table is basically:
user_id int (FK for users table)
tx_type tinyint (a credit = 1)
ON transactions.user_id = users.id
WHERE users.visible = 1
AND ( SELECT MAX(date_created)
WHERE user_id = users.id
AND tx_type = 1 ) <=
HAVING SUM(transactions.amount) > 0