jrdev22 jrdev22 - 1 month ago 14
SQL Question

SQL Job - last runtime & last successful runtime

I want to have a view about my sql jobs which contains

name
job_id
run_date
LastSuccessfulRunDate


This is what i found for the last successful run dates.
Works like a charm, but here i am, failing to add the last run date.

Use msdb
GO

SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM
dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = 1
GROUP BY SJ.name, JH.run_status
ORDER BY [Last Time Job Ran On] DESC
GO


Here's what i got so far but it's giving me the result in two lines, one with last run date and the other with last successful run (which is als incorrect because it selects the highest date there is - distinct value). I kindly ask for help to get the join right and the result in one line.

USE msdb
GO
SELECT DISTINCT SJ.Name AS JobName,
SJH.run_date AS LastRunDate,
SJH.job_id,
(SELECT MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME))
FROM sysjobs
RIGHT JOIN sysjobhistory
ON SJ.job_id = SJH.job_id
WHERE SJH.run_status = 1) AS LastSuccessfulRun,

CASE SJH.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Successful'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS LastRunStatus

FROM sysjobhistory SJH, sysjobs SJ

WHERE SJH.job_id = SJ.job_id and SJH.run_date =
(SELECT MAX(SJH.run_date) FROM sysjobhistory SJH WHERE SJ.job_id = SJH.job_id)

Answer

solution is:

    SELECT (SELECT name
                  FROM      sysjobs
                  WHERE   (job_id = sjh.job_id)) AS job_name, MAX(sjh.run_date) AS last_succesful_run_date, MAX(sjh.run_time) AS last_succesful_run_time,
                      (SELECT MAX(run_date) AS Expr1
                       FROM      sysjobhistory
                       WHERE   (run_status = 0)) AS last_unseccesful_run_date,
                      (SELECT MAX(run_time) AS Expr1
                       FROM      sysjobhistory AS sysjobhistory_1
                       WHERE   (run_status = 0)) AS last_unseccesful_run_time
FROM     sysjobhistory AS sjh INNER JOIN
                  sysjobs AS sj ON sjh.job_id = sj.job_id
WHERE  (sjh.run_status = 1)
GROUP BY sjh.job_id
ORDER BY job_name, last_succesful_run_date, last_succesful_run_time

dropped the status case bc it's clear enough this way. the case was not mandatory.