SQL Server – How to Get Average of Job History Times

jobssql servert-sql

The following is a script I found online that has most of the elements of what I desire in the result set. The only thing missing is how I can get an average of the run times over a decided time period, like a week, 10 days, 23 days, a month, etc.

I've done some searching, but what I've tried has failed in some way or another. Further, my TSQL are weak, but I'm reading publications by Itzik Ben-gan to change this matter. I'd truly appreciate any help that anyone can provide.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

Best Answer

Updated to satisfy the new requirement. Note that you may be tempted to use CONVERT(TIME, but resist - this is brittle, in the event any job runs longer than 24 hours.

SELECT 
  job_name = name, 
  avg_sec  = rd,
  avg_hhmm = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2)
FROM
(
  SELECT
    j.name, 
    rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' 
       + CONVERT(VARCHAR(6),run_duration),6),5,0,':'),3,0,':')))
  FROM msdb.dbo.sysjobhistory AS h
  INNER JOIN msdb.dbo.sysjobs AS j
  ON h.job_id = j.job_id
  WHERE h.step_id = 0
  GROUP BY j.name
) AS t
ORDER BY job_name;
Related Question