sql server - select average of top 3 for each specific sql job -

select  jobs.job_id jobid ,                      jobs.name jobname ,                      act.start_execution_date startexecutiondate ,                      avg(floor(run_duration / 100)) avgdurationmin ,                      case   		--if job average less 5 minutes limit avg+10 minutes                           when avg(floor(run_duration / 100)) <= 5                           ( avg(floor(run_duration / 100)) ) + 2  		--if job average greater 5 minutes limit avg*limit percentage                           else ( avg(floor(run_duration / 100))                                  )                      end durationlimit ,                      datediff(mi, act.start_execution_date, getdate()) [currentduration]                 @currently_running_jobs crj                      inner join msdb..sysjobs jobs on crj.job_id = jobs.job_id                      inner join msdb..sysjobactivity act on act.job_id = crj.job_id                                                                , act.stop_execution_date null                                                                , act.start_execution_date not null                      inner join msdb..sysjobhistory hist on hist.job_id = crj.job_id                                                                , hist.step_id = 0                crj.job_state = 1 --and jobs.name = 'long_running_testing'              group jobs.job_id ,                      jobs.name ,                      act.start_execution_date ,                      datediff(mi, act.start_execution_date, getdate())  					having  case when avg(floor(run_duration / 100)) <= 5  							  (avg(floor(run_duration / 100))) + 2                              --then ( avg(floor(run_duration / 100)) ) + 1                             else ( avg(floor(run_duration / 100))                                  )                      end < datediff(mi, act.start_execution_date, getdate())

can please me extract average run_duration of recent 3 entries of job's partitioned job_name?

please find below script used extract information of recent 3 run_duration partitioned job_name, calculates average of run_duration .

here need recent 3 run_duration average count.

select top 1  ties  jobs.job_id jobid , avg(floor(run_duration/100%100 )),                     jobs.name jobname ,                     act.start_execution_date startexecutiondate   msdb..sysjobs jobs inner join msdb..sysjobactivity act on act.job_id = jobs.job_id inner join msdb..sysjobhistory hist on hist.job_id = jobs.job_id , hist.step_id = 0   group jobs.job_id ,                     jobs.name              ,      act.start_execution_date ,hist.run_duration,                    datediff(mi, act.start_execution_date, getdate()) order  case when row_number() on (partition jobs.name order hist.run_duration desc) <= 3 0 else 1 end; 

perhaps cte simplify it.

with seqbyjob_cte (  select  jobs.name jobname                             ,hist.run_duration                             ,row_number() over(partition jobs.name order act.start_execution_date desc) seq                         msdb..sysjobs jobs                             inner join msdb..sysjobactivity act on act.job_id = jobs.job_id                             inner join msdb..sysjobhistory hist on hist.job_id = jobs.job_id , hist.step_id = 0) select  avg(run_duration) avgof3mostrecentruns         ,case      --if job average less 5 minutes limit avg+10 minutes                      when avg(floor(run_duration / 100)) <= 5                      ( avg(floor(run_duration / 100)) ) + 2     --if job average greater 5 minutes limit avg*limit percentage                      else ( avg(floor(run_duration / 100))                             )                 end durationlimit         ,jobname    seqbyjob_cte   seq <= 3 group jobname