First N longest running jobs per day

--- Author : Calin Damian
--- Creation Date : 2002-05-28
--- Purpose : Query that displays first N longest running jobs per day
--- Comments: 	conversion from time stored as integer in SQL server tables into
--- 		the ##.##.## format is lenghty and ugly...you can hide it in a function
--- 		and come with a better implementation


USE msdb

DECLARE @N int
SET @N = 3

SELECT 	J.name , 
	CAST (CAST (H.run_date AS varchar) AS datetime) as DateWhenRun, 
	STUFF (STUFF (
		 REPLICATE ('0', 6 - LEN (CAST (H.run_time AS varchar)) )  
		 + CAST (H.run_time AS varchar), 3,0 ,':'
  	       ), 6, 0, ':') as TimeWhenRun,
	STUFF (STUFF (
		 REPLICATE ('0', 6 - LEN (CAST (H.run_duration AS varchar)) )  
		 + CAST (H.run_duration AS varchar), 3,0 ,':'
  	       ), 6, 0, ':') as Duration

FROM dbo.sysjobs J
JOIN dbo.sysjobhistory H
ON H.job_id = J.job_id
JOIN 
	(SELECT H.run_date , H.run_duration 
	FROM dbo.sysjobhistory H
	JOIN dbo.sysjobhistory H1 
	ON 	H.run_date = H1.run_date
		AND H.run_duration <=H1.run_duration
		AND  H.step_name = '(Job Outcome)' AND H1.step_name = '(Job Outcome)' 
	GROUP BY H.run_date , H.run_duration
	HAVING COUNT (*)  < = @N) GH
ON 	GH.run_date = H.run_date 
	AND GH.run_duration = H.run_duration
	AND H.step_name = '(Job Outcome)'

ORDER BY H.run_date DESC, H.run_duration DESC