Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Monthly SQL Server Agent Jobs report Expand / Collapse
Author
Message
Posted Monday, October 15, 2007 2:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 12, 2015 8:35 AM
Points: 847, Visits: 52
Comments posted to this topic are about the item Monthly SQL Server Agent Jobs report




-- Amit



"There is no 'patch' for stupidity."



Download the Updated SQL Server 2005 Books Online.
Post #410729
Posted Monday, May 11, 2009 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 25, 2014 9:51 AM
Points: 1, Visits: 9
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
Post #714389
Posted Friday, August 14, 2015 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 12, 2016 9:45 AM
Points: 20, Visits: 68
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

Post #1711813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse