February 3, 2006 at 6:06 am
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.
i have a table with these 5 fields.
"AltNumb" "AltName" "TimeLastRun" "TimeNextRun" "skdDuration"
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.
heres's the short lil proc below:
CREATE PROCEDURE dbo.Check_Time_Duration
AS
declare @myDate smalldatetime
select @myDate = getdate()
SELECT AlertNumber from time_check2 where DATEDIFF(MINUTE,timeLastRun,@myDate) = Schedule_Duration
what i'd like to do is if anything matches, is Update the two columns "TimeLastRun" & "TimeNextRun" right on the spot based on the AlertNumber(if that row matches in the above query).
something like this:
DECLARE @myDate smalldatetime
select @myDate = getDate()
Update time_check2 set timelastrun=@myDate,timenextrun=dateadd(minute,5,@myDate)where AlertNumber ='ALT001'
BUT the AlertNumber of course matched in the first select
Hopefully someone can see what i'm trying to accomplish here and give me some help
thanks again
rik
February 3, 2006 at 7:17 am
If I'm understanding you...this should work...
Update time_check2
set TimeLastRun = @mydate,
TimeNextRun = dateadd(minute,skdDuration,@myDate)
from time_check2 c
join
(
SELECT AlertNumber from time_check2 where DATEDIFF(MINUTE,timeLastRun,@myDate) = Schedule_Duration
)q
on q.AlertNumber = c.AlertNUmber
HTH
Mathew J Kulangara
sqladventures.blogspot.com
February 3, 2006 at 7:52 am
i never thought of that. so it only does the update if those self-joined AlertNumbers match? and Still Returns the results?
i'm sorry but that is totally brilliant,simple and elegant
thanks so much
rik
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply