Hello,

We are trying to receive a daily notification on SQL agent jobs status via HTML which uses the below code:

SELECT td = [sJOB].[name],       '',
                    td = CASE WHEN [sJOBH].[run_date] IS NULL THEN NULL ELSE SUBSTRING(CAST([sJOBH].[run_date] AS CHAR(8)),5,2) + '/' + RIGHT(CAST([sJOBH].[run_date] AS CHAR(8)),2) + '/' + LEFT(CAST([sJOBH].[run_date] AS CHAR(8)),4) END, '',
                    td = CASE WHEN [sJOBH].[run_time] IS NULL THEN NULL ELSE LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) END, '',
                    td = CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' END, '',
                    td = STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
              FROM [msdb].[dbo].[sysjobs] AS [sJOB]
										LEFT JOIN ( SELECT  [job_id], MIN([next_run_date]) AS [NextRunDate], MIN([next_run_time]) AS [NextRunTime] FROM [msdb].[dbo].[sysjobschedules] GROUP BY [job_id]) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
										LEFT JOIN ( SELECT  [job_id], [run_date], [run_time], [run_status], [run_duration], [message], ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1
			ORDER BY [Name]

Above code executes successfully but does not seem to work as expected.

Like it does not give the value for those Jobs currently running or are in state of Unknown or for those who runs monthly and does not have a history listed and returns NULL.

what can be wrong here?

Also, can i have a better way of getting report on daily basis say once a day which gives me the status of all my jobs either running failed success etc.

We have notifications enabled for failure and success but just for a report basis we need to have summary of jobs status .

Please help!

DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 3 days  
SET @Year = DATEPART(yyyy, @PreviousDate)  
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) 
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) 
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) 
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT) 

-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
FROM     msdb.dbo.sysjobhistory h 
         INNER JOIN msdb.dbo.sysjobs j 
           ON h.job_id = j.job_id 
         INNER JOIN msdb.dbo.sysjobsteps s 
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE    h.run_date > @FinalDate 
ORDER BY h.instance_id DESC 
 

Another query:

select sh.job_id,name,date_created,date_modified,run_date,run_duration,run_time ,run_status  from msdb..sysjobhistory sh join msdb..sysjobs sj on sh.job_id=sj.job_id
where name=''