select enabled_scheduled_jobs.name ,isnull(success_jobs.number_of_success_runs,0) number_of_success_runs ,isnull(success_jobs.total_duration_for_success_runs_secs,0) total_duration_for_success_run_secs ,isnull(not_success_jobs.number_of_not_success_runs,0) number_of_not_success_runs ,isnull(not_success_jobs.total_duration_for_not_success_runs_secs,0) total_duration_for_not_success_run_secs from ( select distinct sysjobs.job_id,name from msdb.dbo.sysjobs inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id where enabled = 1 ) enabled_scheduled_jobs
LEFT JOIN
( -- listing of all job runs and run_durations by job name for jobs with a success status select sysjobhistory.job_id ,count(*) number_of_success_runs ,sum ( SUBSTRING(RIGHT(REPLICATE('0', 6) + CAST(run_duration as varchar(6)), 6),5,2) +(SUBSTRING(RIGHT(REPLICATE('0', 6) + CAST(run_duration as varchar(6)), 6),3,2) * 60) +(SUBSTRING(RIGHT(REPLICATE('0', 6) + CAST(run_duration as varchar(6)), 6),1,2) * 60 * 60)) total_duration_for_success_runs_secs from msdb.dbo.sysjobhistory where step_id = 0 and run_status = 1 group by sysjobhistory.job_id ) success_jobs
ON enabled_scheduled_jobs.job_id = success_jobs.job_id
LEFT JOIN
( -- listing of all job runs and run_durations by job name for jobs with a non-success status select sysjobhistory.job_id ,count(*) number_of_not_success_runs ,sum ( SUBSTRING(RIGHT(REPLICATE('0', 6) + CAST(run_duration as varchar(6)), 6),5,2) +(SUBSTRING(RIGHT(REPLICATE('0', 6) + CAST(run_duration as varchar(6)), 6),3,2) * 60) +(SUBSTRING(RIGHT(REPLICATE('0', 6) + CAST(run_duration as varchar(6)), 6),1,2) * 60 * 60) ) total_duration_for_not_success_runs_secs from msdb.dbo.sysjobhistory where step_id = 0 and run_status != 1 group by sysjobhistory.job_id ) not_success_jobs
ON enabled_scheduled_jobs.job_id = not_success_jobs.job_id
