Hi all.
I have a field called "lastlogin". It creates a current date automatically when my users login.

I need to create a query that my IT guy can run to check all the users in the database at call up all the dates that are 90 days old and 120 days old.

I then need all those matching email addresses to get sent an asp cdont mailing to tell them that their account has not been active for 90 days.

I have and use the CDONT mailer, which works perfectly.

What would my query be? Canrt figure it out.

Then, i assume I need an "if" statement that says "if date >90 then" and calls the mailer code, if date =>120 then mail this info and so on. right?

I dont know that i would use an if statement, in my mind the sql code would get to ugly. I would probably create 2 temp tables, one for accounts that are 90 days old and one for 120 days old and then send your mailings based upon the rows in those tables.

To populate the tables i would use something like
INSERT INTO #tmp90(userName)
SELECT userName from [Table] where Convert(varchar(10), lastActivity, 101) = Convert(varchar(10), getdate() - 90, 101)

INSERT INTO #tmp120(userName)
SELECT userName from [Table] where Convert(varchar(10), lastActivity, 101) = Convert(varchar(10), getdate() - 120, 101)

Alternatively you can use just one table
INSERT INTO #tmp(userName)
SELECT userName from [Table] where Convert(varchar(10), lastActivity, 101) = Convert(varchar(10), getdate() - 120, 101) or Convert(varchar(10), lastActivity, 101) = Convert(varchar(10), getdate() - 90, 101)

Those will give you the correct result sets.

Also, the reason that I use the Convert is because doing getdate() is going to return a Date and Time but you want all records for a specific date so, if you were to just use getdate() -90 you might get 05/31/2007 09:20:54:236 so, any account that had activity before or after 9:20 would not show up in your result set.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

Thanks,
What is the 101 number indicating? Also, what is the lastactivity
? that is the field, right?

lastActivity in my example is the lastlogin column in your database.

101 specifies a date format, in this case MM/DD/YYYY

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========

Hi Guys,

I am giving a very Simple Method to write your query.

(1) To find the User's List that last login is more then 90 days old and less then 120 days.

Select UserId
From User_Master
Where DateDiff(day,Login_DateTime,GetDate()) between 90 and 120

(2) To find the User's List that last login is more then 120 days old.

Select UserId
From User_Master
Where DateDiff(day,Login_DateTime,GetDate())>=120

Try & Enjoy your Work.

Dharam,
I am using mySQL. Your syntax returns a syntax error on GetDate().

My field that contains the last log in date is "lastlogin".

Hi melkin,

Actually i am using sql server.

I think for mysql this will work-

Select UserId
From User_Master
Where (TO_DAYS(lastlogin)-TO_DAYS(curdate())) between 90 and 120

u can also try by this one for mysql.

select *
from usertable
where lastlogin >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)

if DATE_SUB() function is running on your mysql u can make query by using this.

Thanks.

I am now using the SQL statement as:
SQL ="SELECT * from customerlogin_test where lastlogin < DATE_ADD(CURRENT_DATE, INTERVAL -90 DAY) ORDER BY lastlogin DESC"

how can I now set this up as an "if" statement or another way, so I can have it check, 90 days, 120 days, 150 days and 155 days, the delete it.

each date range I set will send an auto email letting that user know they are getting close to being removed.

Thanks again!

Can anyone help me?
how can I now set this up as an "if" statement or another way, so I can my code check 90 days, 120 days, 150 days and 155 days, the delete it.

each date range I set will send an auto email letting that user know they are getting close to being removed.

I am using the SQL statement as:
SQL ="SELECT * from customerlogin_test where lastlogin < DATE_ADD(CURRENT_DATE, INTERVAL -90 DAY) ORDER BY lastlogin DESC"

Thanks all.