December 3, 2009 at 6:55 am
[font="Courier New"]This script below renders job name -AND- average duration for each job. (The AvgDuration is an INTEGER value derived by averaging sysjobhistory run_duration)
SELECT DISTINCT j.name, AVG(h.run_duration) As AvgDuration
FROM sysjobs j
INNER JOIN sysjobhistory h ON h.job_id=j.job_id
INNER JOIN
(SELECT job_id, MAX(STR(run_date,8)+STR(run_time,8)) as LastRunDate
FROM sysjobhistory GROUP BY job_id) x
ON j.job_id=x.job_id
GROUP BY j.name
ORDER BY j.name
Example values from this query translate to:
AvgDuration --> HH MM SS
----------- -- -- --
13301 --> 1 33 01
1627 --> 16 27
114 --> 1 14
5 --> 5
Looking for SUBSTRING or CAST/CONVERT syntax to render:
13301 --> 1:33:01
1627 --> 16:27
114 --> 1:14
5 --> :05
[/font]
December 3, 2009 at 7:27 am
May not be exactly what you are looking for but it works:
declare @time1 int;
set @time1 = 13715;
select stuff(stuff(right('000000' + cast(@time1 as varchar), 6),5,0,':'),3,0,':')
You should also note that the AVG value you are computing isn't accurate as it is doing integer arithmetic not time arithmetic when adding the elapsed times.
December 3, 2009 at 7:35 am
Try this:
SELECT DISTINCT j.name
,AVG(h.run_duration)
,substring(convert(varchar(25),(dateadd(ss
,AVG(h.run_duration)
,convert(datetime,convert(varchar(10),getdate(),121))
)
),121)
,11,9) As AvgDuration
FROM sysjobs j
INNER JOIN sysjobhistory h ON h.job_id=j.job_id
INNER JOIN
(SELECT job_id, MAX(STR(run_date,8)+ STR(run_time,8)) as LastRunDate
FROM sysjobhistory GROUP BY job_id) x
ON j.job_id=x.job_id
GROUP BY j.name
ORDER BY j.name
-Vikas Bindra
December 3, 2009 at 7:42 am
Go with Lynn. Mine is wrong in your scenario.
It converts Second in integer value to HH:MM:SS format
-Vikas Bindra
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply