hey guys very new to sql server - i can take care of the basic CRUD but,i'm not familiar with some of the syntax to handle stuff right in the proc. i've been making multiple round trips to accomplish what i think i should be able to do based on a condition right when im in the procedure itself. so, please bear with me.
ALT001 FIRST ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:35:00 PM 5
ALT002 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:43:00 PM 10
ALT003 SECOND ALERT 2/2/2006 12:42:00 PM 2/2/2006 2:48:00 PM 15
i have a service that will call a procedure that takes the current_timestamp and compares it (datediff) with the column "TimeLastRun" and see's if it's equal with the "SkdDuration" - some are set to 5, 10, 15 minutess etc.
For determine which alert number is up, you can do this
declare @myDate smalldatetime
select @myDate = getdate()
where DATEDIFF(MINUTE, TimeLastRun, @myDate) >= Schedule_Duration
Note the > sign.
For updating the TimeLastRun & TimeNextRun :
set TimeLastRun = @myDate,
TimeNextRun = dateadd(minute, Schedule_Duration, @myDate)
from time_check2 U
where DATEDIFF(MINUTE, TimeLastRun, cast(getdate() as smalldatetime)) >= Schedule_Duration