View Single Post
  #2 (permalink)  
Old August 29th, 2007, 08:25 AM
dparsons dparsons is offline
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