DECLARE @jobs TABLE (
    [name]    sysname          NOT NULL
   ,[job_id]  UNIQUEIDENTIFIER NOT NULL
   ,[failed]  BIT              NOT NULL
        DEFAULT 0
   ,[lastRun] DATETIME         NULL
);

INSERT INTO @jobs ([name], [job_id])
SELECT
    [s].[name]
    ,[s].[job_id]
    FROM [msdb].[dbo].[sysjobs] [s]
    WHERE [s].[name] IN (
            'D00480 - ActivePos_read Snapshot'
        );


SELECT
    CONVERT(VARCHAR(20), SERVERPROPERTY('ServerName')) AS [ServerName]
   ,[j].[name]                                         AS [job_name]
   ,CASE [j].[enabled]
        WHEN 1 THEN 'Enabled'
        ELSE 'Disabled'
    END                                                AS [job_status]
   ,CASE [jh].[run_status]
        WHEN 0 THEN 'Error Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
        ELSE 'Status Unknown'
    END                                                AS [last_run_status]
   ,[ja].[run_requested_date]                          AS [last_run_date]
   ,CONVERT(
        VARCHAR(10)
       ,CONVERT(DATETIME, RTRIM(19000101))
        + ([jh].[run_duration] * 9 + [jh].[run_duration] % 10000 * 6 + [jh].[run_duration] % 100 * 10) / 216e4
       ,108
    )                                                  AS [run_duration]
   ,[ja].[next_scheduled_run_date]
   ,CONVERT(VARCHAR(500), [jh].[message])              AS [step_description]
  FROM([msdb].[dbo].[sysjobactivity]     [ja]
  JOIN @jobs [j2] ON j2.[job_id] = ja.[job_id]
  LEFT JOIN [msdb].[dbo].[sysjobhistory] [jh]
      ON [ja].[job_history_id] = [jh].[instance_id])
  JOIN [msdb].[dbo].[sysjobs_view] [j]
      ON [ja].[job_id] = [j].[job_id]
 WHERE [ja].[session_id] = (SELECT MAX([session_id])FROM [msdb].[dbo].[sysjobactivity])
 ORDER BY [job_status] DESC,[last_run_status];