Compare jobs executions duration in a period of time against average executions duration in another period of time

compare_jobs_avg_executions_times.sql

DECLARE	@baseline_start_date datetime	= '2021-11-01';
DECLARE	@baseline_end_date datetime		= '2021-11-05';
DECLARE	@compare_start_date datetime	= '2021-11-08';
DECLARE	@compare_end_date datetime		= '2021-11-12';
DECLARE	@seconds_threshold int = 30;

WITH jobs_runstats_baseline AS
(
	SELECT
		J.job_id AS job_id
	,	J.[name] AS job_name
	,	AVG((CAST(LEFT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) as int) * 3600) + (CAST(SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 3, 2) AS int) * 60) + CAST(RIGHT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) AS int)) as avg_run_time_secs
	FROM	msdb.dbo.sysjobs AS J
	JOIN	msdb.dbo.sysjobhistory AS H
	  ON	J.job_id = H.job_id
	WHERE	H.step_id = 0 AND H.run_duration >= 0
	  AND	H.run_date BETWEEN CAST(CONVERT(varchar(8), @baseline_start_date, 112) AS int) AND CAST(CONVERT(varchar(8), @baseline_end_date, 112) AS int)
	GROUP BY
		J.job_id
	,	J.[name]
)
,	jobs_runstats_compare AS
(
	SELECT
		J.job_id AS job_id
	,	J.[name] AS job_name
	,	CAST(CAST(H.run_date AS varchar(8)) + ' ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(H.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') AS datetime) AS start_execution_date
	,	DATEADD(second, (CAST(LEFT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) as int) * 3600) + (CAST(SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 3, 2) AS int) * 60) + CAST(RIGHT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) AS int), CAST(CAST(H.run_date AS varchar(8)) + ' ' + STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(H.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') AS datetime)) AS stop_execution_date
	,	(CAST(LEFT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) as int) * 3600) + (CAST(SUBSTRING(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 3, 2) AS int) * 60) + CAST(RIGHT(RIGHT(REPLICATE('0', 6) +  CAST(H.run_duration as varchar(6)), 6), 2) AS int) AS run_time_secs
	FROM	msdb.dbo.sysjobs AS J
	JOIN	msdb.dbo.sysjobhistory AS H
	  ON	J.job_id = H.job_id
	WHERE	H.step_id = 0 AND H.run_duration >= 0
	  AND	H.run_date BETWEEN CAST(CONVERT(varchar(8), @compare_start_date, 112) AS int) AND CAST(CONVERT(varchar(8), @compare_end_date, 112) AS int)
)
SELECT
	JB.job_name
,	JC.start_execution_date
,	JC.stop_execution_date
,	JB.avg_run_time_secs
,	JC.run_time_secs
,	JC.run_time_secs - JB.avg_run_time_secs AS delta_run_time_secs
FROM	jobs_runstats_baseline AS JB
JOIN	jobs_runstats_compare AS JC
  ON	JB.job_id = JC.job_id
WHERE
	JC.run_time_secs - JB.avg_run_time_secs >= @seconds_threshold
ORDER BY 
	JB.job_name