select /*+ no_index(erp eis_rs_processes_idx3 eis_rs_processes_idx4) */
err.report_name,
err.report_id "EIS Report ID",
era.application_name application,
errc.category_name category,
to_char(erp.start_time,'yyyy-mm') month,
count(*) runs,
count(case when erp.start_time-ers.logon_time>1 then 1 end) scheduled_runs,
count(*)-count(case when erp.start_time-ers.logon_time>1 then 1 end) ad_hoc_runs,
count(case when erp.submission_source='XL Connect' then 1 end) "XL Connect Runs",
count(distinct erp.created_by) users,
count(case when erp.status in ('E','T') then 1 end) errors,
round(avg((erp.end_time-erp.start_time)*86400)) average_seconds,
round(avg(erp.rows_retrieved)) average_rows
from
xxeis.eis_rs_processes erp,
xxeis.eis_rs_sessions ers,
xxeis.eis_rs_reports err,
xxeis.eis_rs_applications era,
xxeis.eis_rs_report_categories errc
where
1=1 and
erp.submission_source in ('eXpress','XL Connect') and
erp.session_id=ers.session_id and
erp.report_id=err.report_id and
err.application_id=era.application_id and
err.category_id=errc.category_id
group by
err.report_name,
err.report_id,
era.application_name,
errc.category_name,
to_char(erp.start_time,'yyyy-mm')
order by
err.report_name,
month |
