SELECT j.[name],

CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +

STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime) AS [LastRun],

CASE jh.run_status WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Success'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Canceled'

WHEN 4 THEN 'In progress'

END AS [Status]

FROM (SELECT a.job_id,MAX(a.instance_id) As [instance_id]

FROM msdb.dbo.sysjobhistory a

WHERE a.step_id = 0

GROUP BY a.job_id) b

INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id=b.instance_id

INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id