October 15, 2007 at 2:47 am
Comments posted to this topic are about the item Monthly SQL Server Agent Jobs report
May 11, 2009 at 11:55 am
Thanks for the query. Do you have the new version where you have taken care of jobs that run twice or more in a day.
Sachin
August 14, 2015 at 8:01 am
Here is a version of your nice script that uses CTE to avoid temp table. It's also re-formatted with SQL Prompt.
DECLARE @year int = 2015
DECLARE @month tinyint = 8
;WITH jobs
AS ( SELECT j.name AS [JobName] ,
SUBSTRING(CONVERT(varchar, run_date), 7, 2) AS [Day] ,
MAX(CASE run_status
WHEN 1 THEN 'S'
WHEN 0 THEN 'F'
WHEN 2 THEN 'R'
WHEN 3 THEN 'C'
ELSE 'P'
END) AS [Status]
FROM msdb..sysjobhistory h ,
msdb..sysjobs j
WHERE j.enabled = 1
AND j.job_id = h.job_id
AND run_date BETWEEN ( ( @year * 10000 ) + ( @month * 100 )
+ 1 )
AND ( ( @year * 10000 ) + ( @month * 100 )
+ 32 )
AND h.step_id = 0
GROUP BY j.name ,
SUBSTRING(CONVERT(varchar, run_date), 7, 2)
)
SELECT
JobName ,
max(case Day when '01' then Status else '' end ) As [01],
max(case Day when '02' then Status else '' end ) As [02],
max(case Day when '03' then Status else '' end ) As [03],
max(case Day when '04' then Status else '' end ) As [04],
max(case Day when '05' then Status else '' end ) As [05],
max(case Day when '06' then Status else '' end ) As [06],
max(case Day when '07' then Status else '' end ) As [07],
max(case Day when '08' then Status else '' end ) As [08],
max(case Day when '09' then Status else '' end ) As [09],
max(case Day when '10' then Status else '' end ) As [10],
max(case Day when '11' then Status else '' end ) As [11],
max(case Day when '12' then Status else '' end ) As [12],
max(case Day when '13' then Status else '' end ) As [13],
max(case Day when '14' then Status else '' end ) As [14],
max(case Day when '15' then Status else '' end ) As [15],
max(case Day when '16' then Status else '' end ) As [16],
max(case Day when '17' then Status else '' end ) As [17],
max(case Day when '18' then Status else '' end ) As [18],
max(case Day when '19' then Status else '' end ) As [19],
max(case Day when '20' then Status else '' end ) As [20],
max(case Day when '21' then Status else '' end ) As [21],
max(case Day when '22' then Status else '' end ) As [22],
max(case Day when '23' then Status else '' end ) As [23],
max(case Day when '24' then Status else '' end ) As [24],
max(case Day when '25' then Status else '' end ) As [25],
max(case Day when '26' then Status else '' end ) As [26],
max(case Day when '27' then Status else '' end ) As [27],
max(case Day when '28' then Status else '' end ) As [28],
max(case Day when '29' then Status else '' end ) As [29],
max(case Day when '30' then Status else '' end ) As [30],
max(case Day when '31' then Status else '' end ) As [31]
FROM jobs
GROUP BY JobName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply