SQLServer Jobhistory -最后一次成功运行时和最后不成功运行时

Question

我想了解一下我的sql作业，它包含

name job_id run_date LastSuccessfulRunDate

这是我最后一次成功的运行日期的发现。工作很有魅力，但我在这里，没有添加最后的运行日期。

    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

这是我到目前为止得到的结果，但是它给了我两行结果，一行是最后一次运行日期，另一行是最后一次成功运行(这是als不正确的，因为它选择了最高日期-不同的值)。我恳请帮助，以获得正确的连接和结果在一行。

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 1

解决办法是：

SELECT sj.name,
        MAX(dbo.agent_datetime(sjh.run_date, sjh.run_time)) AS last_Succesful_run,
        x.last_Unsuccesful_run
FROM    sysjobhistory sjh
        INNER JOIN sysjobs sj ON sjh.job_id = sj.job_id
LEFT OUTER JOIN
     (SELECT sysjobs.name,
                MAX(dbo.agent_datetime(sysjobhistory.run_date, sysjobhistory.run_time)) AS last_Unsuccesful_run
        FROM    sysjobhistory
        INNER JOIN sysjobs ON sysjobhistory.job_id = sysjobs.job_id
        WHERE sysjobhistory.run_status = 0
        GROUP BY sysjobs.name) x
        ON x.name = sj.name
WHERE   run_status = 1 
GROUP BY sj.job_id, sj.name, last_Unsuccesful_run

放弃了公元前的状态，这样就足够清楚了。这一案件不是强制性的。

Answer 2

我想你想要row_number()

SELECT x.*
FROM (SELECT SJ.NAME AS [Job Name], RUN_STATUS AS [Run Status],
             DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS LastRunDateTime,
             ROW_NUMBER() OVER (PARTITION BY SJ.NAME ORDER BY DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) DESC) as seqnum
      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 
     ) x
WHERE seqnum = 1
ORDER BY LastRunDateTime DESC;

另外，学习使用正确、显式的JOIN语法。FROM子句中的逗号是古老的。