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