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