Remove duplicate records in stored procedure

Last Post 02 Jan 2008 07:27 AM by nosepicker. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Amber_Robertsona
New Member
New Member

--
28 Dec 2007 03:15 AM
Dear All, I have created the stored procedure below to display most recent record of none user defined SQL Server Jobs status. I want the stored procedure to only show 1 record per SQL Server Job. However I have noticed that when a job has had two different jobs status in the sysjobhistory table the stored procedure will generate 2 records for the same job name. Does anybody know how I could change the stored procedure to only display one record per SQL Server job?

CREATE PROCEDURE udfAR_DBARelatedOnlyServerJobStatus
As

/* Created by Amber Robertson on 27th December 2007
Purpose: Display Only None User Defined Enabled Job status information for SQL DBA Dashboard.*/

SELECT
Job.[name] As [Job Name]
,max(Hist.run_date) As [Completed Date]
,max(Hist.run_time) As [Completed Time]


,case
WHEN
Hist.run_status = 1
THEN
'Succeeded'

WHEN
Hist.run_status = 0
THEN
'Failed'
WHEN
Hist.run_status = 3
THEN
'Canceled'

WHEN
Hist.run_status =4
THEN
'In Progress'

END AS
'Job Status'
FROM
sysjobhistory As Hist
inner join sysjobs As Job
on Hist.job_id = Job.job_id
inner join sysjobschedules As Sche
on Sche.job_id = Job.job_id
inner join syscategories As Cat
on Cat.category_id =job.category_id

WHERE
Job.enabled = 1
AND Cat.[Name] not like 'User Defined'

GROUP BY
Job.[name]
,Hist.run_status
,Cat.[name]

ORDER BY
Job.[name]



Any suggestions much appreciated.

Kind Regards
Amber
nosepicker
New Member
New Member

--
31 Dec 2007 08:18 AM
Assuming that you want the most recent occurrence of a job from the jobhistory table, you can use a subquery to get the max instance_id, and then join that back to the jobhistory table:

SELECT
Job.[name] As [Job Name]
,max(Hist.run_date) As [Completed Date]
,max(Hist.run_time) As [Completed Time]


,case
WHEN
Hist.run_status = 1
THEN
'Succeeded'

WHEN
Hist.run_status = 0
THEN
'Failed'
WHEN
Hist.run_status = 3
THEN
'Canceled'

WHEN
Hist.run_status =4
THEN
'In Progress'

END AS
'Job Status'
FROM
(SELECT job_id, MAX(instance_id) AS instance_id
FROM sysjobhistory
GROUP BY job_id) AS Hist1
JOIN sysjobhistory AS Hist
ON Hist1.job_id = Hist.job_id
AND Hist1.instance_id = Hist.instance_id

inner join sysjobs As Job
on Hist.job_id = Job.job_id
inner join sysjobschedules As Sche
on Sche.job_id = Job.job_id
inner join syscategories As Cat
on Cat.category_id =job.category_id

WHERE
Job.enabled = 1
AND Cat.[Name] not like 'User Defined'

GROUP BY
Job.[name]
,Hist.run_status
,Cat.[name]

ORDER BY
Job.[name]
Amber_Robertsona
New Member
New Member

--
02 Jan 2008 01:11 AM
Hi, thank you and provides precisely the information I wanted. Would you mind explaining why you used the instance_id? As I noticed that the instance_id doesn't go up as the run_date and run_time increases.


Kind Regards
Amber
SQLUSA
New Member
New Member

--
02 Jan 2008 02:33 AM
Your query was refocused to the last jobid entry in the history table by the clever use of a GROUP BY and table SELF-JOIN.

Kalman Toth
htttp://www.sqlusa.com
Amber_Robertsona
New Member
New Member

--
02 Jan 2008 04:01 AM
Kalman, thank you for the explanation given.


Kind Regards
Amber
nosepicker
New Member
New Member

--
02 Jan 2008 07:27 AM
That explanation answered your question? Ok then...
Amber_Robertsona
New Member
New Member

--
03 Jan 2008 02:37 AM
That explanation answered my quesiton. The Solution you supplied was perfect.

Thank you.

Regards
Amber


Acceptable Use Policy
---