July 21, 2016 at 12:42 pm
I have first query which gives me list of all jobs with column duration ( but that duration is not in proper format so have to calculate to find out exact hours/minutes/seconds)
second query does that calculation
I am unable to merge both query so that my job duration column will give me minutes/hours/second in duration column.
can someone help merge this two queries so i can see duration in proper format ?
======================
here are the queries
===================
First Query:
SELECT h.server, j.name, h.run_date, h.run_time, h.run_duration
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON h.job_id = j.job_id
ORDER BY j.name;
Second Query:
SELECT
h.run_duration
, CASE LEN(h.run_duration)
WHEN 1 THEN '00:00:0' + CONVERT(CHAR(1),h.run_duration)
WHEN 2 THEN '00:00:' + CONVERT(CHAR(2),h.run_duration)
WHEN 3 THEN '00:0' + CONVERT(CHAR(1),LEFT(h.run_duration,1)) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))
WHEN 4 THEN '00:' + CONVERT(CHAR(2),LEFT(h.run_duration,2)) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))
WHEN 5 THEN '0' + CONVERT(CHAR(1),LEFT(h.run_duration,1)) + ':' + LEFT(RIGHT(h.run_duration,4),2) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))
ELSE
CONVERT(VARCHAR(4),LEFT(h.run_duration,LEN(h.run_duration)-4)) + ':' + LEFT(RIGHT(h.run_duration,4),2) + ':' + CONVERT(CHAR(2),RIGHT(h.run_duration,2))
END AS My_Custom_Run_Duration
FROM
msdb..sysjobhistory h
July 21, 2016 at 12:51 pm
Just add the column to your first query. Here's a shorter version.
SELECT h.server
, j.name
, h.run_date
, h.run_time
, h.run_duration
, STUFF(STUFF(RIGHT(1000000+h.run_duration, 6), 5, 0, ':'), 3, 0, ':') AS My_Custom_Run_Duration
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobhistory AS h ON h.job_id = j.job_id
ORDER BY j.name;
July 22, 2016 at 8:38 am
;
WITH MScte
AS (
SELECT h.server,
h.job_id,
h.run_duration,
h.run_date,
h.run_time,
CASE LEN(h.run_duration) WHEN 1 THEN '00:00:0' + CONVERT(CHAR(1), h.run_duration)
WHEN 2 THEN '00:00:' + CONVERT(CHAR(2), h.run_duration)
WHEN 3 THEN '00:0' + CONVERT(CHAR(1), LEFT(h.run_duration, 1)) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2)) WHEN 4 THEN '00:' + CONVERT(CHAR(2), LEFT(h.run_duration, 2)) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2))
WHEN 5 THEN '0' + CONVERT(CHAR(1), LEFT(h.run_duration, 1)) + ':' + LEFT(RIGHT(h.run_duration, 4), 2) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2))
ELSE CONVERT(VARCHAR(4), LEFT(h.run_duration, LEN(h.run_duration) - 4)) + ':' + LEFT(RIGHT(h.run_duration, 4), 2) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2))
END AS My_Custom_Run_Duration
FROM msdb..sysjobhistory h
)
SELECT h.SERVER
,j.NAME
,h.run_date
,h.run_time
,h.run_duration
FROM msdb.dbo.sysjobs j
inner join MScte h
--INNER JOIN msdb.dbo.sysjobhistory h
ON h.job_id = j.job_id
ORDER BY j.NAME;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply