Merge Two Queries !!

Question

Post reply

Merge Two Queries !!

rk1980factor

SSCommitted

Points: 1681
More actions
July 21, 2016 at 12:42 pm

#328032

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sunita2912 SSC Veteran Points: 295 More actions · Answer 2

;

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;