I have a table where I do logging for all the SQL Server Jobs, I had a requirement where I had to calculate the idle time between two jobs to find the load on the server and better utilise the idle time of the server for other maintenance related operations, for a Job I track many parameters, but the once that are interested for me now for this query are the Job Start Time and the Job End Time, . That is I had to calculate time difference between the last job end time to the current job start time.
For example, in the below table, I need to know the time difference between the Job End Time for Job ID 1 and Job Start Time of Job ID 2, that’s 120 minutes.
JobID JobStartTime JobEndTime
1 2011-01-07 02:55:00.000 2011-01-07 03:55:00.000
2 2011-01-07 05:55:00.000 2011-01-07 06:55:00.000
3 2011-01-07 08:55:00.000 2011-01-07 09:55:00.000
Let me create a temporary table and show how I did it.
CREATE TABLE #JobLog(
JobID int identity(1,1), JobStartTime DATETIME,JobEndTime DATETIME)
INSERT INTO #JobLog(JobStartTime,JobEndTime)
VALUES('2011-01-07 02:55:00.000','2011-01-07 03:55:00.000')
INSERT INTO #JobLog(JobStartTime,JobEndTime)
VALUES('2011-01-07 05:55:00.000','2011-01-07 06:55:00.000')
INSERT INTO #JobLog(JobStartTime,JobEndTime)
VALUES('2011-01-07 08:55:00.000','2011-01-07 09:55:00.000')
Checking for table and it’s records.
SELECT * FROM #JobLog
Time difference between Last Job End Time and Current Job Start Time
WITH CTE as( SELECT JobID, JobStartTime, JobEndTime, ( SELECT MAX(T1.JobEndTime) FROM #JobLog T1 WHERE T1.JobEndTime < T2.JobStartTime ) AS Previous_JobEndTime FROM #JobLog T2 ) SELECT JobID, JobStartTime, JobEndTime , DATEDIFF(MI,Previous_JobEndTime,JobStartTime) AS IdleTime FROM CTE
And the below is the resuly.
JobID JobStartTime JobEndTime IdleTime
1 2011-01-07 02:55:00.000 2011-01-07 03:55:00.000 NULL
2 2011-01-07 05:55:00.000 2011-01-07 06:55:00.000 120
3 2011-01-07 08:55:00.000 2011-01-07 09:55:00.000 120
dropping the temporary table
DROP TABLE #JobLog
Comments on this entry are closed.