Sql Job : Traffic Light Status - depending on last 3 execution results

Recently came across interesting requirement from business.
Sql jobs keeps loading/refreshing data for projects. If you are not showing last data refresh time in report then end users will not be having idea about data updates. They will start panicking saying reports not showing correct data and you cannot send update to all 1000 users that last refresh is successful.

So we wanted to show traffic light status (Green/Yellow/Red) for job executions on SharePoint site.

In case of any doubt End user can directly visit page and see the respective jobs Status.
Logic was

The logic is as per Business expectations.
Last 1 job failure/Successful àGreen.
Last 2 job failures à Yellow
Last 3 job failures à Red.

Below is the query for getting jobs status

WITH RunTotalTestData AS (       SELECT         CASE             WHEN JobExecutionNo = 1 THEN Run_Status * 100             WHEN JobExecutionNo = 2 THEN Run_Status * 10             WHEN JobExecutionNo = 3 THEN Run_Status * 1         END         RunStat,         k.JobName,         k.StartTime,         k.run_status,         k.ExecutionStatus       FROM (                   SELECT                     name AS JobName,                     CONVERT(datetime, CONVERT(char(8), run_date) + ' ' + STUFF(STUFF((LEFT('000000', 6 - LEN(run_time)) + CONVERT(varchar(6), run_time)), 3, 0, ':'), 6, 0, ':')) AS StartTime,                     DATEADD(MINUTE, DATEDIFF(MINUTE, '0:00:00', CONVERT(time, STUFF(STUFF((LEFT('000000', 6 - LEN(run_duration)) + CONVERT(varchar(6), run_duration)), 3, 0, ':'), 6, 0, ':'))), CONVERT(datetime, CONVERT(char(8), run_date) + ' ' + STUFF(STUFF((LEFT('000000', 6 - LEN(run_time)) + CONVERT(varchar(6), run_time)), 3, 0, ':'), 6, 0, ':'))) AS EndTime,                     CASE run_status                         WHEN 1 THEN 1                         ELSE 0                     END AS run_status,                     CASE run_status                         WHEN 0 THEN 'failed'                         WHEN 1 THEN 'Succeded'                         WHEN 2 THEN 'Retry'                         WHEN 3 THEN 'Cancelled'                         WHEN 4 THEN 'In Progress'                     END AS ExecutionStatus,                     instance_id,                     jh.job_id,                     ROW_NUMBER() OVER (PARTITION BY name ORDER BY run_date DESC, run_time DESC) AS JobExecutionNo                   FROM msdb.dbo.sysjobhistory jh                   INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id                   WHERE step_id = 0 AND j.name LIKE ('%Daily%')               )k       WHERE JobExecutionNo < 4 ) SELECT JobName,   CASE     WHEN Last3Exec = 0 THEN 'Red'     WHEN Last3Exec < 10 THEN 'Yellow'     WHEN Last3Exec >= 10 THEN 'Green'   END Flag,   CASE     WHEN Last3Exec = 0 THEN 2     WHEN Last3Exec < 10 THEN 1     WHEN Last3Exec >= 10 THEN 0   END FlagVal,   CASE     WHEN Last3Exec >= 10 THEN 'Job Running all Good'     ELSE 'Job is running very slow due to Performance Issue.'   END AS DisplayMessage,   Last3Exec FROM (SELECT a.JobName,SUM(a.RunStat) AS Last3Exec FROM RunTotalTestData a GROUP BY a.JobName) K GO

It will display Result-set as below,

-Thanks,

Kiran Jagtap.