Information on sysjobhistory
Information on sysjobs

— shows job history with the most recent one to show the most recent one with it’s run status
select job_name, run_datetime, run_duration, run_status
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration,
run_status
from
(
select
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT(‘000000’ + CONVERT(varchar(6), run_duration), 6),
run_status = CASE run_status WHEN 1 Then 'Success'
WHEN 0 Then 'Failure'
WHEN 2 Then 'Retry'
WHEN 3 Then 'Cancelled'
WHEN 4 Then 'In-Progress'
Else 'Unknown: ' + Convert(VARCHAR(2), run_status)
End
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
where step_id = 0 — show first step
) t
) t
order by run_datetime desc, job_name

Output:
Test 2010-01-28 20:15:43.000 00:00:00 Failure
Test 2010-01-28 19:37:33.000 00:00:00 Success

Code used from: http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx