last_autotask

 

with last_autotask as (
select max( trunc(window_start_time)) max_start_time , b.client_name
from DBA_AUTOTASK_JOB_HISTORY b
where trunc(WINDOW_START_TIME) > trunc(sysdate- 14) group by client_name ), autotask_job as (
select b.client_name, sum(EXTRACT (SECOND
FROM job_duration) + extract ( minute
from job_duration) * 60 + extract ( hour
from job_duration) * 60 *60 ) job_duration , trunc(window_start_time) LastStartTime
from DBA_AUTOTASK_JOB_HISTORY a, last_autotask b
where trunc(window_start_time) = max_start_time and a.client_name = b.client_name group by b.client_name,trunc(window_start_time) )
select /*+ opt_param('_optimizer_cost_model' 'cpu') */ sys_context('userenv','DB_NAME') db_name , a.client_name, status, to_char(to_date(mod(job_duration,86400) ,'sssss'),'HH24:MI:SS') Job_duration, LastStartTime
from DBA_AUTOTASK_CLIENT a, autotask_job b
where a.client_name = b.client_name(+) order by a.client_name