--************************
-- This script returns a (graphical) timeline for all SQL jobs
--************************

--Pre Cleanup
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#JobNames') IS NOT NULL DROP TABLE #JobNames;
GO

SET NOCOUNT ON

DECLARE @Minutes TABLE (DT DATETIME)
DECLARE @Minutes2 TABLE (col2set VARCHAR(250) NOT NULL PRIMARY KEY)
DECLARE
@DT DATETIME, @StartDT DATETIME, @EndDT DATETIME,
@Resolution INT, @RemoveNonactiveJobs INT, @IgnoreDisabledJobs INT, @ShowJobCollisionsOnly INT

SET @StartDT = getdate() - 8 --how far back to look, in days
SET @EndDT = getdate()
SET @Resolution = 15 -- Enter the Resolution in minutes
SET @RemoveNonActiveJobs = 1
SET @IgnoreDisabledJobs = 1
SET @ShowJobCollisionsOnly = 1 --only brings back times when 2 or more jobs were running (set to -1 if you want to show the no-jobs-running timeslots)

--***************************
-- Make a Jobname table
--***************************
SELECT DISTINCT
replace(NAME,',', '') AS JobName --Replace commas (they mess up @cols variable used lower in the code)
INTO #JobNames
FROM msdb.dbo.sysjobs
WHERE enabled = @IgnoreDisabledJobs

--***************************
-- Generate a Datetime table between StartDT and EndDT with x minute Resolution
--***************************
SET @DT = @StartDT

WHILE @DT <= @EndDT
BEGIN
 INSERT INTO @Minutes (DT) VALUES (@DT)

 SET @DT = dateadd(mi, @Resolution, @DT)
END

--***************************
-- Create a timeline table by crossjoining the Datetime and Jobnames tables
--***************************
SELECT DT,
 JobName,
 0 AS Active
INTO #Timeline
FROM @Minutes
CROSS JOIN #JobNames

--***************************
-- Create the Job Runtime information table
--***************************
SELECT
 replace(NAME,',', '') AS NAME, --Replace commas (they mess up @cols variable used lower in the code)
 CAST(
  SUBSTRING(CAST(run_date as varchar(8)),1,4)+'-'+
  SUBSTRING(CAST(run_date as varchar(8)),5,2)+'-'+
  SUBSTRING(CAST(run_date as varchar(8)),7,2)+' '+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),1,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),3,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),5,2)
 as datetime)'SDT',
 CAST(
  SUBSTRING(CAST(run_date as varchar(8)),1,4)+'-'+
  SUBSTRING(CAST(run_date as varchar(8)),5,2)+'-'+
  SUBSTRING(CAST(run_date as varchar(8)),7,2)+' '+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),1,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),3,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),5,2)
 as datetime)+
 CAST(
  '1900-01-'+
  SUBSTRING(RIGHT('00'+CAST(run_duration/10000/24 +1 as varchar(4)),2),1,2)+' '+
  SUBSTRING(RIGHT('00'+CAST(run_duration/10000%24 as varchar(4)),2),1,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_duration as varchar(6)),6),3,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_duration as varchar(6)),6),5,2)
 as datetime)
 'EDT'
INTO #JobRuntime
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobhistory his ON his.job_id = job.job_id
AND step_id = 0 --only gives back Job history, not Step history
AND job.NAME NOT IN ('Database Mirroring Monitor Job','<dbname>')
AND 
 CAST(
  SUBSTRING(CAST(run_date as varchar(8)),1,4)+'-'+
  SUBSTRING(CAST(run_date as varchar(8)),5,2)+'-'+
  SUBSTRING(CAST(run_date as varchar(8)),7,2)+' '+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),1,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),3,2)+':'+
  SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(6)),6),5,2)
 as datetime) BETWEEN @StartDT AND @EndDT
ORDER BY SDT

--***************************
-- Update the Timeline based on the the Job Runtime information table
--***************************
UPDATE #Timeline
SET Active = 1
FROM #Timeline
INNER JOIN #JobRuntime ON JobName = NAME 
AND(SDT BETWEEN dt AND dateadd(mi, @Resolution - 1, DT) -- Start point (added for Resolution support)
 OR EDT BETWEEN dt AND dateadd(mi, @Resolution, DT) -- End point (added for Resolution support)
 OR  DT BETWEEN SDT AND EDT
)

--***************************
-- Delete all jobs from the Timeline that that had no activity
--***************************
IF @RemoveNonactiveJobs = 1
 DELETE
 FROM #Timeline
 WHERE JobName NOT IN(
   SELECT Jobname
   FROM #Timeline
   WHERE active = 1
   GROUP BY Jobname
   )

--***************************
-- SQL 2000: Show Job Run Details
--***************************
IF @@VERSION LIKE '%SQL Server  2000%'
 SELECT DISTINCT 
 counts.DT,
 counts.TotalRunningJobs,
 t.JobName
 FROM #Timeline t
 INNER JOIN
 (SELECT COUNT(DISTINCT JobName)'TotalRunningJobs',DT
  FROM #Timeline
  WHERE active=1
  GROUP BY DT
  HAVING COUNT(DISTINCT JobName) > @ShowJobCollisionsOnly
 )counts ON t.DT=counts.DT AND t.active=1
 ORDER BY 1 DESC,2 DESC,3

--***************************
-- SQL 2005 and later: Pivot the Timeline table
--***************************
IF @@VERSION NOT LIKE '%SQL Server  2000%'
BEGIN
CREATE TABLE #Pivot (
 col1 VARCHAR(250) NULL,
 col2 VARCHAR(250) NULL,
 col3 INT NULL
 )

-- col1 = row, col2 = column, col3 = data
INSERT INTO #Pivot
SELECT DISTINCT
 convert(VARCHAR(250), DT, 120) DT,
 JobName,
 Active
FROM #Timeline

-- Make a table with all unique col2 values
INSERT INTO @Minutes2
SELECT DISTINCT col2
FROM #Pivot

--SELECT * FROM #Pivot --debug check

DECLARE @cols AS NVARCHAR(MAX);SET @cols = '' --in SQL 2000, change MAX to 4000
DECLARE @sql AS NVARCHAR(MAX);SET @sql = ''   --in SQL 2000, change MAX to 4000

SELECT @cols = @cols +
CASE
 WHEN @cols = '' THEN ''
 ELSE ',' + CHAR(10) --add comma and a newline
END
+ quotename(col2set)
FROM @Minutes2
GROUP BY col2set
ORDER BY col2set

--PRINT @cols --debug option

-- Build the pivot statement as a dynamic sql statement
SET @sql = N'
SELECT 
col1 as Date,
' + REPLACE(@cols,',','+') + N'as TotalRunningJobs,' + 
@cols + N'
FROM
(SELECT *
 FROM #Pivot
)AS D 
PIVOT
(MAX(col3)
 FOR col2 IN(' +@cols + N')
)AS P 
WHERE ' +REPLACE(@cols,',','+') + ' > '+CAST(@ShowJobCollisionsOnly as nvarchar(10))+'
ORDER BY 1'

--PRINT @sql --debug option

--***************************
-- Output the Timeline table
--***************************
EXEC sp_executesql @sql
END --of the SQL 2005+ IF statement

--Post Cleanup
IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline;
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..#Pivot') IS NOT NULL DROP TABLE #Pivot;
IF OBJECT_ID('tempdb..#JobNames') IS NOT NULL DROP TABLE #JobNames;