Average Run Time of a SQL Server Job

To get the average run time of a SQL Server job the below code will get that information from sysjobhistory.

SELECT

       name,

       start_time,

       dateadd(s, avg(run_duration_seconds), start_time) as average_end_time,

       dateadd(s, max(run_duration_seconds), start_time) as max_end_time,

       dateadd(s, min(run_duration_seconds), start_time) as min_end_time

FROM

       (SELECT

              j.name,

              cast('01/01/1900 ' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 1, 2) + ':' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 3, 2) + ':' +

              substring(right(('000000') + cast(jh.run_time as varchar), 6), 5, 2) as smalldatetime) as start_time,

              substring(right(('000000') + cast(jh.run_duration as varchar), 6), 5, 2) +

              (substring(right(('000000') + cast(jh.run_duration as varchar), 6), 3, 2) * 60) +

              (substring(right(('000000') + cast(jh.run_duration as varchar), 6), 1, 2) * 60 * 60) as run_duration_seconds

       FROM sysjobs j

              INNER JOIN sysjobhistory jh on j.job_id = jh.job_id

       WHERE

              datepart(weekday, cast(substring(cast(jh.run_date as varchar), 5, 2) + '-' + substring(cast(jh.run_date as varchar), 7, 2) + '-' + substring(cast(jh.run_date as varchar), 1, 4) as smalldatetime)) in (2,3,4,5,6)

WHERE run_duration_seconds > 120

GROUP BY

       name,

       start_time

ORDER BY

       name