Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jwurtz
Starting Member
3 Posts |
 Posted - 2005-07-07 : 14:04:37
|
| I working on a sql script that will send an alert to users every 30 days (30,60,90,120...). I am having problems coming with the script that will do that without doing something like this:WHERE DATEDIFF(d, rs("GameDay"), GETDATE()) = 30 OR DATEDIFF(d, rs("GameDay"), GETDATE()) = 60 OR ..... and so on.I am able to do this is asp with doing a simple for/next loop but I am needing to run this in a stored procedure on my SQL server each night at a certain time. |
|
|
jhermiz
3564 Posts |
Posted - 2005-07-07 : 15:28:00
|
Why wouldnt you just do it for every day evenly divisble by 30 ?That way you dont have to do checks for 30, 60 , 90, 120, 150...all you'd have to do is divide the result by 30 and if it returns integer (evenly divisible) then send hte message else dont..Consider:2530509025/30 = no good30/30 = good divisible50/30 = 1 20/30 no good since it returns a float60/30 = good divisible...
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
 |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2005-07-07 : 16:06:13
|
| We did something similar for password notifications. Users will automatically get an email saying that there password will expire in 10, 5, 2, and 1 days. To dertermine who to send a email to we did a simple select where the days till their password expires was in a table that contained a row for each day we wanted to send an email on.We did this because the days we wanted wasn't an even distribution. I think you could do something similar.- Eric |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-07-07 : 16:11:06
|
Mod operator
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-07 : 17:41:08
|
| More specific to what John was saying:IF DATEPART(dy, getdate()) % 30 = 0 BEGIN (run your script)ENDELSE ... |
|
|
|
jwurtz
Starting Member
3 Posts |
Posted - 2005-07-08 : 01:20:19
|
| MOD would work in an ASP page but I don't believe it would work in a Stored Procedure. Here is what I came up with and it seems to work.DECLARE @N int, @Days int, @DaysDiff intSET @DaysDiff = 30SET @N = 1WHILE @N <= 50BEGIN SET @Days = (@DaysDiff * @N) SELECT statement here WHERE (DATEDIFF(d, dbo.tblTableName.DateCreated, GETDATE()) = @Days) SET @N = @N + 1ENDThis way it returns a result for 30/60/90 and so on up to (30 * 50) days. Thanks for all the ideas. |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-08 : 13:13:16
|
| The percent sign (%) is the MOD operator in T-SQL. |
|
|
|
jwurtz
Starting Member
3 Posts |
Posted - 2005-07-08 : 14:12:55
|
| nosepicker I didn't know that. I started recently using stored procedures for everything I do and I am still learning some of the ins and outs of them.Thanks.... |
|
|
|
|
|
|
|
|
Sending Alerts 30/60/90 and so on...