Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 29th, 2007, 08:11 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need to query dates after 90 days old

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?

Reply With Quote
  #2 (permalink)  
Old August 29th, 2007, 08:25 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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

================================================== =========
Reply With Quote
  #3 (permalink)  
Old August 31st, 2007, 11:06 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old August 31st, 2007, 01:05 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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

================================================== =========
Reply With Quote
  #5 (permalink)  
Old September 25th, 2007, 04:36 AM
Authorized User
 
Join Date: Dec 2006
Location: Mumbai, Maharastra, India.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



Reply With Quote
  #6 (permalink)  
Old September 26th, 2007, 11:53 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

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

Reply With Quote
  #7 (permalink)  
Old September 27th, 2007, 02:11 AM
Authorized User
 
Join Date: Dec 2006
Location: Mumbai, Maharastra, India.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #8 (permalink)  
Old September 27th, 2007, 02:17 AM
Authorized User
 
Join Date: Dec 2006
Location: Mumbai, Maharastra, India.
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #9 (permalink)  
Old October 8th, 2007, 02:12 PM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #10 (permalink)  
Old October 30th, 2007, 08:03 AM
Authorized User
Points: 483, Level: 7
Points: 483, Level: 7 Points: 483, Level: 7 Points: 483, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2007
Location: Edgewood, NY, USA.
Posts: 92
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Query (Dates) Neal Access VBA 5 November 20th, 2007 09:08 AM
Select Query with dates mikedeepak SQL Server ASP 1 July 12th, 2007 05:34 AM
Help for Last 30 Business Days Query srinath2003 SQL Server 2000 3 March 21st, 2005 02:37 PM
Calculating number of days between multiple dates Vann Access 4 December 3rd, 2004 08:26 PM
How to calculate the work days beetwen two dates. andres_pm Javascript 1 October 7th, 2004 06:23 AM



All times are GMT -4. The time now is 10:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.