SQL Server每日选取两个作业中最晚完成记录的查询需求

要实现每日从指定的两个作业中选取完成时间最晚的记录，你可以借助ROW_NUMBER() OVER(PARTITION BY ...)函数按日期分组，再筛选每组内时间最晚的条目。以下是修改后的完整查询：

WITH JobHistoryWithRank AS (
    SELECT 
        sj.name JobName,
        ISNULL(sjs.step_name, 'Job Status') StepName,
        dbo.agent_datetime(sjh.run_date, sjh.run_time) RunDateAndTime,
        CASE sjh.run_status
            WHEN 0 THEN 'Failed'
            WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry'
            WHEN 3 THEN 'Canceled'
            WHEN 4 THEN 'In Progress'
        END RunStatus,
        -- 按日期分区，同一日期内按完成时间倒序编号
        ROW_NUMBER() OVER(
            PARTITION BY CAST(dbo.agent_datetime(sjh.run_date, sjh.run_time) AS DATE) 
            ORDER BY dbo.agent_datetime(sjh.run_date, sjh.run_time) DESC
        ) rn
    FROM msdb.dbo.sysjobs sj
    INNER JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
    LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON sjh.job_id = sjs.job_id AND sjh.step_id = sjs.step_id  
    WHERE (sj.name = 'Daily Cube Processing - Pt1 - T2' AND sjh.step_id = 11) 
       OR (sj.name = 'Daily Exec Summ-Agent Only Cube Processing' AND sjh.step_id = 6)
)
SELECT JobName, StepName, RunDateAndTime, RunStatus
FROM JobHistoryWithRank
WHERE rn = 1 -- 筛选每组内编号为1（即时间最晚）的记录
ORDER BY RunDateAndTime DESC;

关键逻辑说明 #

1. 分区依据：用CAST(RunDateAndTime AS DATE)提取日期，确保同一天的作业记录被分到同一个分组中。
2. 排序编号：ROW_NUMBER()函数在每个日期分组内，按RunDateAndTime从晚到早排序，给每条记录分配一个序号，最晚的记录序号为1。
3. 筛选结果：外层查询只保留序号为1的记录，即每日完成时间最晚的那条作业记录。

执行上述查询后，输出结果将与你期望的一致：

JobName                               StepName                 RunDateAndTime      RunStatus
Daily Cube Processing - Pt1 - T2      COE_Daily_Export_BIW     2023-08-09 07:20:20.000 Succeeded
Daily Exec Summ-Agent Only Cube Processing  Agent Only Tab Calculate    2023-08-08 04:47:18.000 Succeeded
Daily Exec Summ-Agent Only Cube Processing  Agent Only Tab Calculate    2023-08-07 05:40:18.000 Succeeded
Daily Cube Processing - Pt1 - T2      COE_Daily_Export_BIW     2023-08-06 14:24:33.000 Succeeded

内容的提问来源于stack exchange，提问作者Debasis