How to find SQL agent job status in SQL Server using Query

 To find SQL server job status using SQL query can be very useful for automation, I have used below to kick off another SQL agent job with respect to status of current job.

;WITH CTE_SQLAGENT_JOB_STATUS

AS (

    SELECT DISTINCT NAME AS [JobName]

        ,s.step_id      ,s.step_name

        ,CASE

            WHEN [Enabled] = 1   THEN 'Enabled' ELSE 'Disabled'

            END [JobStatus]

        ,CASE

            WHEN SJH.run_status = 0 THEN 'Failed'

            WHEN SJH.run_status = 1 THEN 'Succeeded'

            WHEN SJH.run_status = 2 THEN 'Retry'

            WHEN SJH.run_status = 3 THEN 'Cancelled'

            WHEN SJH.run_status = 4 THEN 'In Progress'

            ELSE 'Unknown'

            END [JobOutcome]

        ,CONVERT(VARCHAR(8), sjh.run_date) [RunDate]

        ,CONVERT(VARCHAR(8), STUFF(STUFF(CONVERT(TIMESTAMP, RIGHT('000000' + CONVERT(VARCHAR(6), sjh.run_time), 6)), 3, 0, ':'), 6, 0, ':')) RunTime

        ,RANK() OVER (  PARTITION BY s.step_name ORDER BY sjh.run_date DESC

                ,sjh.run_time DESC   ) AS rn

        ,SJH.run_status

    FROM msdb..SYSJobs sj

    INNER JOIN msdb..SYSJobHistory sjh ON sj.job_id = sjh.job_id

    INNER JOIN msdb.dbo.sysjobsteps s ON sjh.job_id = s.job_id

    AND sjh.step_id = s.step_id

    WHERE (sj.NAME LIKE 'JOB NAME')

    AND sjh.run_date = CONVERT(CHAR, getdate(), 112)

    )

SELECT  [JobName]

FROM CTE_SQLAGENT_JOB_STATUS

WHERE rn = 1

AND run_status NOT IN (1,4)

and [JobName] = 'Job Name