All,
I hate the formatting myself so thought I would share. This code was "borrowed" from Mike Pearson, thanks Mike...
As you can see it's all in the CASE.
select j.name,
case when h.step_name ='(Job outcome)' then '(Package Total)' else h.step_name end
, h.run_status,
CASE len(h.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(h.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(h.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(h.run_duration,3),1)
+':' + right(h.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(h.run_duration,5),1)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(h.run_duration,6),2)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
END as 'Duration'
,h.run_duration
from dbo.sysjobs j
inner join dbo.sysjobhistory h
on j.job_id=h.job_id
where j.name like '%somejobName%'
order by j.name, h.step_name