select distinct
count(x.run_id) over (partition by x.report_id) count,
x.report_name,
count(distinct x.run_by) over (partition by x.report_id) users,
max(x.run_by) keep (dense_rank last order by x.count_per_user, x.run_by) over (partition by x.report_id) most_frequent,
count(distinct x.run_by) over () total_users,
x.created_by,
xxen_util.client_time(x.created_date) creation_date,
x.last_modified_by last_updated_by,
xxen_util.client_time(x.last_modified_date) last_update_date
from
(select
ors.run_id,
ors.component run_component,
ors.report_id,
ors.report_name,
ors.report_type,
ors.run_by,
ors.created_by,
ors.created_date,
ors.completion_time,
ors.time_in_seconds,
ors.output_type,
ors.error_message,
ors.last_modified_by,
ors.last_modified_date,
count(ors.run_id) over (partition by ors.report_id, ors.created_by) count_per_user
from
(
select
ors.id run_id,
ors.component,
orv.report_id,
orv.report_name,
orv.report_type,
xxen_orb_util.orb_user(ors.run_by)run_by,
orv.created_by,
cast(orv.created_date as date) created_date,
cast(ors.run_date as date) run_date,
cast(ors.completion_time as date) completion_time,
round((cast(ors.completion_time as date) - cast(ors.created_date as date)) * 86400,2) time_in_seconds,
ors.output_type,
ors.error_message,
ors.rows_retrieved,
orv.last_modified_by,
cast(orv.last_modified_date as date) last_modified_date
from
xxorb.orb_report_stats ors,
xxorb.orb_report_v orv
where 1=1 and
--ors.created_date >=sysdate-30 and
orv.report_id=ors.report_id
)ors
)x |
