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.

a boring table guids , datetimes , sooper seecrit jobnames, redacted

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

Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more