select step_id [Step No],step_name [Step Name],case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration
FROM (select step_id,step_name, CASE
WHEN run_duration > 0
THEN
(run_duration / 1000000) * (3600 * 24)
+ (run_duration / 10000 % 100) * 3600
+ (run_duration / 100 % 100) * 60
+ (run_duration % 100)
ELSE
NULL
END AS LastRunDuration from msdb.dbo.sysjobhistory where job_id='A09DCA56-E235-4A87-A3D7-C3BB9D823F20' and run_status=1 and step_id <> 0
and run_date >= (select MAX(run_date) from msdb.dbo.sysjobhistory where step_name='CHECK_job_to_complete')
) T
FROM (select step_id,step_name, CASE
WHEN run_duration > 0
THEN
(run_duration / 1000000) * (3600 * 24)
+ (run_duration / 10000 % 100) * 3600
+ (run_duration / 100 % 100) * 60
+ (run_duration % 100)
ELSE
NULL
END AS LastRunDuration from msdb.dbo.sysjobhistory where job_id='A09DCA56-E235-4A87-A3D7-C3BB9D823F20' and run_status=1 and step_id <> 0
and run_date >= (select MAX(run_date) from msdb.dbo.sysjobhistory where step_name='CHECK_job_to_complete')
) T
No comments:
Post a Comment