title: SQL Server and sysjobhistory run_status query
body: <p>I'm having trouble getting a query written to do what I want. In SQL Server there is a system table called msdb.dbo.sysjobhistory. It contains the history of the SQL agent jobs that run and perform operations on the server. Each row of the table corresponds to a step in the job. There is a column in the table called run_status, that gets an integer value to indicate the outcome of the job when it completed and got written into the table.</p>
  
  <p>I track the information in this table with some reports and simple queries. I discovered an interesting case last week that I was not accounting for in my reports. In the table, even though each row is a step in the job, there is also a step 0 entry for the job that serves as an overall outcome. Normally I just look at the step 0 rows and get the run_status in that row for the job and use that as the outcome. However it is possible for a step in the job to have a 'failure' status, but the overall job outcome in step 0 has a 'succeeded' status. I came up with the following query to look for these situations:</p>
  
  <pre><code>SELECT job_id, step_id, step_name, run_date, run_time, run_status, W.parent_status
  FROM msdb.dbo.sysjobhistory [H]
  CROSS APPLY 
  ( SELECT TOP 1 run_status AS parent_status
    FROM msdb.dbo.sysjobhistory
    WHERE job_id = H.job_id
    AND run_date &lt;= H.run_date
    AND step_id = 0
    AND (CASE WHEN run_date = H.run_date THEN run_Time ELSE H.run_time END) &lt;= H.run_time
    ORDER BY run_date DESC, run_time DESC
  ) AS [W]
  ORDER BY run_date, run_time, step_id
  </code></pre>
  
  <p>This query will give a result set similar to the following for one particular job_id:</p>
  
  <pre><code>job_id | step_id | step_name | run_date | run_time | run_status | parent_status
  -------------------------------------------------------------------------------
  A12345 |    0    | (outcome) | 20170112 |  40000   | 1          | 1
  A12345 |    1    |   step 1  | 20170112 |  43000   | 1          | 1
  A12345 |    2    |   step 2  | 20170112 |  50000   | 0          | 1
  A12345 |    3    |   step 3  | 20170112 |  53000   | 1          | 1
  </code></pre>
  
  <p>In this situation, a run_status of 0 means failure, and a run_status of 1 means success.
  Now that I have this table, I would like to use it to find out what really happened with a job. I need to make a CASE statement or something similar that will "override" the run_status of step 0 when necessary. So if the situation above occurs, then say I want to make the run_status a value of 2 instead. So now when I query just the job outcome steps I'd get something like this for the above job:</p>
  
  <pre><code>job_id | step_id | step_name | run_date | run_time | run_status 
  ---------------------------------------------------------------
  A12345 |    0    | (outcome) | 20170112 | 40000    |     2
  </code></pre>
  
  <p>I know what I want to do, I am just not sure how to write the SQL to do it. I need to somehow compare the parent status to all of the run_status results, and when parent status = 1 and run_status = 0, then change the run_status of step 0. Thanks for any assistance.</p>