14DayJobStepRuntimes.sql

USE msdb;

/*

Get the runtime of sql agent job and job steps over the last 14 days

*/

DECLARE @JobName SYSNAME = '<job name here>'

;WITH RawData AS
(
SELECT
	jobname = j.name,
	h.step_id,
	h.step_name,
	servername = server,
	RunDay = DATEDIFF(dd,CONVERT(DATETIME, RTRIM(run_date))
	+ (
	run_time * 9
	+ run_time % 10000 * 6
	+ run_time % 100 * 10
	) / 216e4,getdate()),

	startdatetime =
	CONVERT(DATETIME, RTRIM(run_date))
	+ (
	run_time * 9
	+ run_time % 10000 * 6
	+ run_time % 100 * 10
	) / 216e4,
	enddatetime =
	DATEADD
	(ss,
	(CAST(SUBSTRING((RIGHT('0000000' + CONVERT(VARCHAR(7), run_duration), 7)), 1, 3) AS INT) * 60 * 60
	+ CAST(SUBSTRING((RIGHT('0000000' + CONVERT(VARCHAR(7), run_duration), 7)), 4, 2) AS INT) * 60
	+ CAST(SUBSTRING((RIGHT('0000000' + CONVERT(VARCHAR(7), run_duration), 7)), 6, 2) AS INT)
	),
	(CONVERT(DATETIME, RTRIM(run_date))
	+ (
	run_time * 9
	+ run_time % 10000 * 6
	+ run_time % 100 * 10
	) / 216e4)
	),
	durationMinutes = ROUND(
	(CAST(SUBSTRING((RIGHT('0000000' + CONVERT(VARCHAR(7), run_duration), 7)), 1, 3) AS INT) * 60 * 60
	+ CAST(SUBSTRING((RIGHT('0000000' + CONVERT(VARCHAR(7), run_duration), 7)), 4, 2) AS INT) * 60
	+ CAST(SUBSTRING((RIGHT('0000000' + CONVERT(VARCHAR(7), run_duration), 7)), 6, 2) AS INT)
	) / 60.
	,2),

	retries_attempted
FROM sysjobs j (NOLOCK)
JOIN sysjobhistory h
	ON h.job_id = j.job_id
WHERE j.name = @JobName 
and h.step_id <> 0
AND CONVERT(DATE,CONVERT(DATETIME, RTRIM(run_date))
	+ (
	run_time * 9
	+ run_time % 10000 * 6
	+ run_time % 100 * 10
	) / 216e4) >= CONVERT(DATE,DATEADD(DD,-14,GETDATE()))
)
SELECT 
	step_name,
	step_id,
	[14],[13],[12],[11],[10],[9],[8],[7],[6],[5],[4],[3],[2],[1],[0]
FROM 
(
	SELECT Step_name,step_id,durationMinutes,RunDay
    FROM RawData 
	UNION ALL
	SELECT 'total',9999,SUM(durationMinutes),RunDay
	FROM RawData
	GROUP BY RunDay
) 
P PIVOT ( MIN(durationMinutes) FOR P.Runday IN ([14],[13],[12],[11],[10],[9],[8],[7],[6],[5],[4],[3],[2],[1],[0])) AS PVT
 ORDER BY Step_Id