I am trying to write a query to get a list of all SQL Jobs that have failed. The part I am having an issue with is getting the "Last Successful Date" the SQL Job completed without errors. It seems what I am getting is the last "successful step", not the last time the entire SQL Job completed without errors. I am hoping someone can help show my error. Below is my current code, but the "As Last_Successful_Date" subquery is the part I am having an issue with at the moment.
SELECT DISTINCT
J.NAME AS "Job_Name",
J.DESCRIPTION AS "Job_Description",
H.Step_Name AS "Step_Name",
H.MESSAGE AS "Error_Message",
MSDB.dbo.Agent_Datetime(h.run_date, h.run_time) AS Last_Status_Date,
(SELECT MAX(MSDB.dbo.Agent_Datetime(run_date, run_time)) FROM MSDB.dbo.SysJobHistory WHERE run_status = 1 AND job_id = j.job_id) AS Last_Successful_Date
FROM
MSDB.dbo.SysJobHistory H,
MSDB.dbo.SysJobs J
WHERE
J.job_id = H.job_id
AND h.run_date = (SELECT MAX(hi.run_date) FROM MSDB.dbo.SysJobHistory hi WHERE h.job_id = hi.job_id)
AND ((h.run_status = 0) OR (h.run_status = 3))
AND h.step_name <> '(Job outcome)'
AND J.NAME NOT LIKE 'DBAMaint_%'
ORDER BY
J.NAME