April 26, 2020 at 3:20 pm
select final.* from (
select jobname as Job_Name ,stepname as Package_Name ,Execution_id,Job_Date,case run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as Execution_Status
,max(cast(start_time as datetime) )as last_Run_Start_time
,max(cast(end_time as datetime)) as Last_Run_End_Time
,Run_Duration,
Project_name,
Job_id from
(
select firstquery.*,secondquery.* from
(
select distinct JobName,STEPNAME,step_id,run_Status,format(Run_DateTime,'hh:mm') as Run_Datetime,SUBSTRING(run_duration, 1, 2) + ':' +SUBSTRING(run_duration, 3, 2) + ':' +SUBSTRING(run_duration, 5, 2) as [Duration],ExecutionId,Run_DateTime as job_date,run_duration,job_id
from
(SELECT j.name JobName,h.step_name StepName,h.step_id as Step_ID,h.run_status,SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,PATINDEX('%[^0-9]%',SUBSTRING(h.message, NULLIF(CHARINDEX('Execution ID: ', h.message),0)+14 ,20))-1) as ExecutionId,
msdb.dbo.agent_datetime(run_date, run_time) AS Run_DateTime,run_duration = stuff(stuff(right('00000' + cast(h.run_duration as varchar),6),3,0,':'),6,0,':'),j.job_id
FROM
sysjobhistory h inner join sysjobs j ON j.job_id = h.job_id where h.step_id >=1
)a
) firstquery
inner join
(SELECT distinct E.execution_id,
E.project_name,
replace(E.package_name,'.dtsx','') as package_name
-- format(S.start_time,'hh:mm') as package_start_Time
,e.start_time as Start_Time
, e.end_time as End_Time
FROM [SSISDB].[catalog].[executions] E
JOIN [SSISDB].[catalog].[executable_statistics] S ON E.execution_id = S.execution_id
group by E.execution_id
, E.project_name
, ( e.start_time)
,(e.end_time)
,E.package_name
) secondquery
on
firstquery.ExecutionId =secondquery.execution_id
and secondquery.package_name = replace(ltrim(rtrim(firstquery.stepname)),'Data Refresh','')
)a
group by jobname,stepname ,job_date,run_status,execution_id,Start_Time,End_Time,run_duration,job_id,project_name
) final
Need to get the average max run time and min run time ,which i am not able to figure out
something in the format attached .
Any help is appreciated
April 27, 2020 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply