January 14, 2011 at 2:12 pm
If I wanted to calculate the average run time for a given SQL Agent job over the past month...knowing that the run_duration column in sysjobshistory is represent as 33848, meaning 3'hours, 38"Minutes, and 48Seconds...
How would I accomplish this?
run_date run_duration
20101214 33848
20101215 24124
20101216 24745
20101217 24156
20101218 45317
20101220 12612
20101221 112524
20101222 22205
20101225 22724
20101226 13304
20101227 23235
20101229 31409
20101230 41842
20110104 25949
20110105 23143
20110108 22557
20101223 40551
20101224 23614
20101225 23204
20101226 15713
20101228 21853
20101231 35819
20110103 24137
20110106 20349
20110107 25759
20110109 20024
20110110 12631
20110111 31439
20110112 21940
20110113 30315
20110114 22543
The goal here is to say the average time this past month for the job to run was 3 hours and 23minutes and 56 seconds (as an example)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 14, 2011 at 2:32 pm
Convert it to total seconds first, and then you can compute the average seconds.
select
TotalSeconds =((Hours*3600)+(Minutes*60)+Seconds),
*
from
(
select
Hours= duration/10000,
Minutes = (duration/100)%100,
Seconds = duration%100,
*
from
( -- Test Data
select duration = 43856
) a
) aa
Results:
TotalSeconds Hours Minutes Seconds duration
------------ ----------- ----------- ----------- -----------
16736 4 38 56 43856
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply