Avg(Run_Duration) of DTS Jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Avg(Run_Duration) of DTS Jobs

Author

Topic

DConallen
Starting Member

6 Posts

Posted - 2006-08-29 : 11:31:22

Hello all... i'm trying to get the avg(run_duration) from table sysjobhistory ... but i dont know how to add the times correctly...

select job_id, step_name,
run_duration =
CASE
WHEN LEN(run_duration) = 1 THEN '00:00:0' + convert(char(10), run_duration)
WHEN LEN(run_duration) = 2 THEN '00:00:' + convert (char(10), run_duration)
WHEN LEN(run_duration) = 3 THEN '00:0' + substring(convert (char(10), run_duration), 1, 1) + ':' + substring(convert (char(10), run_duration), 2, 2)
WHEN LEN(run_duration) = 4 THEN '00:' + substring(convert (char(10), run_duration), 1, 2) + ':' + substring(convert (char(10), run_duration), 3, 2)
WHEN LEN(run_duration) = 5 THEN '0' + substring(convert (char(10), run_duration), 1, 1) + ':' + substring(convert (char(10), run_duration), 2, 2) + ':' + substring(convert (char(10), run_duration), 4, 2)
WHEN LEN(run_duration) = 6 THEN substring(convert (char(10), run_duration), 1, 2) + ':' + substring(convert (char(10), run_duration), 3, 2) + ':' + substring(convert(char(10), run_duration), 5, 2)
END

from sysjobhistory

DConallen
Starting Member

6 Posts

Posted - 2006-08-29 : 11:36:32

Would like to group by Job_id ...

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-29 : 12:05:28

Well, chalk this up to boredom...

select job_id
,Replicate('0',(6-len(Cast(Avg(run_duration) as varchar(6))))) + Cast(avg(run_duration) as varchar(6))

FROM sysjobhistory
group by job_id

this gives you a nice padded hhmmss per job.... haven't verified the results...

Just stuff the : in if you want the presentation effect.

________________________________________________
"Wow, you have a master's degree in psychology? I will order a vodka martini, thanks"

DConallen
Starting Member

6 Posts

Posted - 2006-08-29 : 12:48:36

ROCK AND ROLL!!! Hooray for boredom!!!

Thanks Vivaldi!

DConallen
Starting Member

6 Posts

Posted - 2006-08-29 : 14:08:42

FINAL Script: Hope this is Helpful to someone...

/*

CREATE procedure [dbo].[Proc_Job_HistoryDetails]

as

BEGIN

*/

CREATE TABLE #Job_MaxRunDate
(
JobID varchar(100),
HistoryLastRun char(8)

)

CREATE TABLE #Job_MinMaxRunTimes
(
JobID varchar(100),
HistoryMaxRunTime char(10),
HistoryMinRunTime char(10)

)

CREATE TABLE #Job_AvgRunTimes
(
JobID varchar(100),
HistoryAvgTime char(10)

)

CREATE TABLE #Job_History
(
JobID varchar(100),
JobName varchar(100),
JobEnabled char(1),
JobOriginatingServer varchar(50),
JobNextRunDate varchar(15),
JobNextRunTime varchar(10),
HistoryMaxRunTime char(10),
HistoryMinRunTime char(10),
JobAvgRunDuration varchar(10),
ScheduleFrequency varchar(10),
ScheduleID char(5),
JobDescription varchar(250),
HistoryRunDuration char(10),
HistoryLastRunDate char(10)

)

-----------------------------------------------------------------------------------------

INSERT INTO #Job_History
(
JobId,
JobName ,
JobEnabled ,
JobOriginatingServer ,
JobNextRunDate ,
JobNextRunTime ,
HistoryMaxRunTime ,
HistoryMinRunTime ,
JobAvgRunDuration ,
ScheduleFrequency ,
ScheduleID ,
JobDescription,
HistoryRunDuration,
HistoryLastRunDate
)

SELECT
jobs.Job_Id ,
Jobs.Name ,
Jobs.Enabled ,
Jobs.Originating_Server ,

JobNextRunDate =
CASE
when len(schedule.Next_Run_Date) = 1 then convert(char(10),schedule.Next_Run_Date)
when len(schedule.Next_Run_Date) > 1 then convert(char(10),(substring((convert(char(8),schedule.Next_Run_Date)),5,2) + '/' + substring((convert(char(8),schedule.Next_Run_Date)), 7,2) + '/' + substring((convert(char(8),schedule.Next_Run_Date)) ,1,4)))

ELSE
convert(char(10),schedule.Next_Run_Date)
END ,

JobNextRunTime =
CASE
when len(schedule.Next_Run_Time) = 4 then '00:'+ substring(convert(char,schedule.Next_Run_Time),1,2)+':'+ substring(convert(char,schedule.Next_Run_Time),2,2)
when len(schedule.Next_Run_Time) = 5 then '0'+ substring(convert(char,schedule.Next_Run_Time),1,1)+':'+ substring(convert(char,schedule.Next_Run_Time),2,2)+':'+substring(convert(char,schedule.Next_Run_Time),4,2)
when len(schedule.Next_Run_Time) = 6 then substring(convert(char,schedule.Next_Run_Time),1,2)+':'+ substring(convert(char,schedule.Next_Run_Time),3,2)+':'+substring(convert(char,schedule.Next_Run_Time),5,2)

ELSE convert(char(10), schedule.Next_Run_Time)

END ,
HistoryMaxRunTime = NULL ,
HistoryMinRunTime = NULL ,
JobAvgRunDuration = NULL ,
ScheduleFrequency = CASE
WHEN schedule.freq_type = 1 THEN 'Once'
WHEN schedule.freq_type = 4 THEN 'Daily'
WHEN schedule.freq_type = 8 THEN 'Weekly'
WHEN schedule.freq_type = 16 THEN 'Monthly'
WHEN schedule.freq_type = 32 THEN 'Monthly Relative'
WHEN schedule.freq_type = 64 THEN 'When SQL Server Agent Starts'
END ,

Schedule.Schedule_Id ,
Jobs.Description,
HistoryRunDuration = NULL,
HistoryLastRunDate = NULL

FROM LFDCDWWP00.msdb.dbo.sysjobs jobs
INNER JOIN LFDCDWWP00.msdb.dbo.sysjobschedules schedule on jobs.job_id = Schedule.Job_Id
WHERE Start_Step_Id = 1

--------------------------------------------------------------------------------------

Insert INTO #Job_MaxRunDate
(
JobId,
HistoryLastRun
)

select job_id, max(run_date)
from sysjobhistory
group by job_id

Update #Job_History

SET HistoryRunDuration =
CASE
WHEN LEN(run_duration) = 1 THEN '00:00:0' + convert(char(10), run_duration)
WHEN LEN(run_duration) = 2 THEN '00:00:' + convert (char(10), run_duration)
WHEN LEN(run_duration) = 3 THEN '00:0' + substring(convert (char(10), run_duration), 1, 1) + ':' + substring(convert (char(10), run_duration), 2, 2)
WHEN LEN(run_duration) = 4 THEN '00:' + substring(convert (char(10), run_duration), 1, 2) + ':' + substring(convert (char(10), run_duration), 3, 2)
WHEN LEN(run_duration) = 5 THEN '0' + substring(convert (char(10), run_duration), 1, 1) + ':' + substring(convert (char(10), run_duration), 2, 2) + ':' + substring(convert (char(10), run_duration), 4, 2)
WHEN LEN(run_duration) = 6 THEN substring(convert (char(10), run_duration), 1, 2) + ':' + substring(convert (char(10), run_duration), 3, 2) + ':' + substring(convert(char(10), run_duration), 5, 2)
END,
HistoryLastRunDate =
CASE
when len(HistoryLastRun) = 1 then convert(char(10),HistoryLastRun)
when len(HistoryLastRun) > 1 then convert(char(10),(substring((convert(char(8),HistoryLastRun)),5,2) + '/' + substring((convert(char(8),HistoryLastRun)), 7,2) + '/' + substring((convert(char(8),HistoryLastRun)) ,1,4)))

ELSE
convert(char(10),HistoryLastRun)
END

FROM #Job_History Jobs
Inner Join sysJobHistory History on Jobs.JobId = History.Job_Id
Inner Join #Job_MaxRunDate LastRun on LastRun.JobId = Jobs.JobId
WHERE LastRun.JobId = History.job_id
and LastRun.HistoryLastRun = History.Run_Date

----------------------------------------------------------------------------------------

Insert INTO #Job_MinMaxRunTimes
(

JobId ,
HistoryMaxRunTime ,
HistoryMinRunTime

)

Select Job_id,
JobMaxTime =
CASE
WHEN LEN(max(run_duration)) = 1 THEN '00:00:0' + convert(char(10), max(run_duration))
WHEN LEN(max(run_duration)) = 2 THEN '00:00:' + convert (char(10), max(run_duration))
WHEN LEN(max(run_duration)) = 3 THEN '00:0' + substring(convert (char(10), max(run_duration)), 1, 1) + ':' + substring(convert (char(10), max(run_duration)), 2, 2)
WHEN LEN(max(run_duration)) = 4 THEN '00:' + substring(convert (char(10), max(run_duration)), 1, 2) + ':' + substring(convert (char(10), max(run_duration)), 3, 2)
WHEN LEN(max(run_duration)) = 5 THEN '0' + substring(convert (char(10), max(run_duration)), 1, 1) + ':' + substring(convert (char(10), max(run_duration)), 2, 2) + ':' + substring(convert (char(10), max(run_duration)), 4, 2)
WHEN LEN(max(run_duration)) = 6 THEN substring(convert (char(10), max(run_duration)), 1, 2) + ':' + substring(convert (char(10), max(run_duration)), 3, 2) + ':' + substring(convert(char(10), max(run_duration)), 5, 2)
END ,

JobMinTime =
CASE
WHEN LEN(min(run_duration)) = 1 THEN '00:00:0' + convert(char(10), min(run_duration))
WHEN LEN(min(run_duration)) = 2 THEN '00:00:' + convert (char(10), min(run_duration))
WHEN LEN(min(run_duration)) = 3 THEN '00:0' + substring(convert (char(10), min(run_duration)), 1, 1) + ':' + substring(convert (char(10), min(run_duration)), 2, 2)
WHEN LEN(min(run_duration)) = 4 THEN '00:' + substring(convert (char(10), min(run_duration)), 1, 2) + ':' + substring(convert (char(10), min(run_duration)), 3, 2)
WHEN LEN(min(run_duration)) = 5 THEN '0' + substring(convert (char(10), min(run_duration)), 1, 1) + ':' + substring(convert (char(10), min(run_duration)), 2, 2) + ':' + substring(convert (char(10), min(run_duration)), 4, 2)
WHEN LEN(min(run_duration)) = 6 THEN substring(convert (char(10), min(run_duration)), 1, 2) + ':' + substring(convert (char(10), min(run_duration)), 3, 2) + ':' + substring(convert(char(10), min(run_duration)), 5, 2)
END

from sysjobhistory
group by job_id

Update #Job_History

SET HistoryMaxRunTime = MinMax.HistoryMaxRunTime,
HistoryMinRunTime = MinMax.HistoryMinRunTime
FROM #Job_History Jobs
Inner Join #Job_MinMaxRunTimes MinMax on Jobs.JobId = MinMax.JobId

-----------------------------------------------------------------------------------------

Insert INTO #Job_AvgRunTimes
(

JobId ,
HistoryAvgTime

)

select job_id,
Replicate('0',(6-len(Cast(Avg(run_duration) as varchar(6))))) + Cast(avg(run_duration) as varchar(6))

FROM sysjobhistory
group by job_id
order by job_id

Update #Job_History

SET JobAvgRunDuration =
CASE
WHEN LEN(AvgRun.HistoryAvgTime) = 1 THEN '00:00:0' + convert(char(10), AvgRun.HistoryAvgTime)
WHEN LEN(AvgRun.HistoryAvgTime) = 2 THEN '00:00:' + convert (char(10), AvgRun.HistoryAvgTime)
WHEN LEN(AvgRun.HistoryAvgTime) = 3 THEN '00:0' + substring(convert (char(10), AvgRun.HistoryAvgTime), 1, 1) + ':' + substring(convert (char(10), AvgRun.HistoryAvgTime), 2, 2)
WHEN LEN(AvgRun.HistoryAvgTime) = 4 THEN '00:' + substring(convert (char(10), AvgRun.HistoryAvgTime), 1, 2) + ':' + substring(convert (char(10), AvgRun.HistoryAvgTime), 3, 2)
WHEN LEN(AvgRun.HistoryAvgTime) = 5 THEN '0' + substring(convert (char(10), AvgRun.HistoryAvgTime), 1, 1) + ':' + substring(convert (char(10), AvgRun.HistoryAvgTime), 2, 2) + ':' + substring(convert (char(10), AvgRun.HistoryAvgTime), 4, 2)
WHEN LEN(AvgRun.HistoryAvgTime) = 6 THEN substring(convert (char(10), AvgRun.HistoryAvgTime), 1, 2) + ':' + substring(convert (char(10), AvgRun.HistoryAvgTime), 3, 2) + ':' + substring(convert(char(10), AvgRun.HistoryAvgTime), 5, 2)
END

FROM #Job_History Jobs
Inner Join #Job_AvgRunTimes AvgRun on Jobs.JobId = AvgRun.JobId

-----------------------------------------------------------------------------------------

Select * from #Job_History
where jobenabled = 1
order by jobName

/*

Drop table #job_history
Drop table #Job_LastRunDate
DROP table #Job_MinMaxRunTimes
drop table #job_avgruntimes

-- Gets average Run_Duration from existing history per job

select job_id,
Replicate('0',(6-len(Cast(Avg(run_duration) as varchar(6))))) + Cast(avg(run_duration) as varchar(6))

FROM sysjobhistory
group by job_id
order by job_id

*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources