select
decode(grouping_id( c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, c.report_date_julian
, 1, c.week_id
, 3, c.ent_period_id
, 7, c.ent_qtr_id
, 15, c.ent_year_id) time_id
,decode(grouping_id( c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ), 0, 1
, 1, 16
, 3, 32
, 7, 64
, 15, 128) period_type_id
, grouping_id( f.organization_id
, f.request_type
, f.asset_group_id
, f.instance_id
, f.department_id
, case
when f.completion_days < b.range1_high or
b.range1_high is null then 1
when f.completion_days >= b.range1_high and
(f.completion_days < b.range2_high or
b.range2_high is null) then 2
when f.completion_days >= b.range2_high and
(f.completion_days < b.range3_high or
b.range3_high is null) then 3
when f.completion_days >= b.range3_high and
(f.completion_days < b.range4_high or
b.range4_high is null) then 4
when f.completion_days >= b.range4_high and
(f.completion_days < b.range5_high or
b.range5_high is null) then 5
when f.completion_days >= b.range5_high and
(f.completion_days < b.range6_high or
b.range6_high is null) then 6
when f.completion_days >= b.range6_high and
(f.completion_days < b.range7_high or
b.range7_high is null) then 7
when f.completion_days >= b.range7_high and
(f.completion_days < b.range8_high or
b.range8_high is null) then 8
when f.completion_days >= b.range8_high and
(f.completion_days < b.range9_high or
b.range9_high is null) then 9
when f.completion_days >= b.range9_high and
(f.completion_days < b.range10_high or
b.range10_high is null) then 10
else 0
end ) grp_id
, f.organization_id
, f.request_type
, f.asset_group_id
, f.instance_id
, f.department_id
, case
when f.completion_days < b.range1_high or
b.range1_high is null then 1
when f.completion_days >= b.range1_high and
(f.completion_days < b.range2_high or
b.range2_high is null) then 2
when f.completion_days >= b.range2_high and
(f.completion_days < b.range3_high or
b.range3_high is null) then 3
when f.completion_days >= b.range3_high and
(f.completion_days < b.range4_high or
b.range4_high is null) then 4
when f.completion_days >= b.range4_high and
(f.completion_days < b.range5_high or
b.range5_high is null) then 5
when f.completion_days >= b.range5_high and
(f.completion_days < b.range6_high or
b.range6_high is null) then 6
when f.completion_days >= b.range6_high and
(f.completion_days < b.range7_high or
b.range7_high is null) then 7
when f.completion_days >= b.range7_high and
(f.completion_days < b.range8_high or
b.range8_high is null) then 8
when f.completion_days >= b.range8_high and
(f.completion_days < b.range9_high or
b.range9_high is null) then 9
when f.completion_days >= b.range9_high and
(f.completion_days < b.range10_high or
b.range10_high is null) then 10
else 0
end bucket_num
, count(*) total_requests
, sum(f.response_days) total_response_days
, sum(f.completion_days) total_completion_days
/* the following columns prefixed with mv_ exist solely to enable
MV to be fast refreshable */
, grouping_id( f.organization_id
, f.request_type
, f.asset_group_id
, f.instance_id
, f.department_id
, case
when f.completion_days < b.range1_high or
b.range1_high is null then 1
when f.completion_days >= b.range1_high and
(f.completion_days < b.range2_high or
b.range2_high is null) then 2
when f.completion_days >= b.range2_high and
(f.completion_days < b.range3_high or
b.range3_high is null) then 3
when f.completion_days >= b.range3_high and
(f.completion_days < b.range4_high or
b.range4_high is null) then 4
when f.completion_days >= b.range4_high and
(f.completion_days < b.range5_high or
b.range5_high is null) then 5
when f.completion_days >= b.range5_high and
(f.completion_days < b.range6_high or
b.range6_high is null) then 6
when f.completion_days >= b.range6_high and
(f.completion_days < b.range7_high or
b.range7_high is null) then 7
when f.completion_days >= b.range7_high and
(f.completion_days < b.range8_high or
b.range8_high is null) then 8
when f.completion_days >= b.range8_high and
(f.completion_days < b.range9_high or
b.range9_high is null) then 9
when f.completion_days >= b.range9_high and
(f.completion_days < b.range10_high or
b.range10_high is null) then 10
else 0
end
, c.ent_year_id
, c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian ) mv_grouping_id
, c.report_date_julian mv_day_id
, c.week_id mv_week_id
, c.ent_period_id mv_period_id
, c.ent_qtr_id mv_ent_qtr_id
, c.ent_year_id mv_ent_year_id
, count(f.response_days) mv_total_response_days
, count(f.completion_days) mv_total_completion_days
, count(*) mv_count
/* end of fast refreshable needed columns */
from
ISC.ISC_MAINT_REQ_WO_F f
, FII.FII_TIME_DAY c
, BIS.BIS_BUCKET_CUSTOMIZATIONS b
, BIS.BIS_BUCKET bb
where
f.completion_date = c.report_date
and bb.short_name = 'BIV_MAINT_REQ_COMP_AGING'
and bb.bucket_id = b.bucket_id
group by
c.ent_year_id
, rollup ( c.ent_qtr_id
, c.ent_period_id
, c.week_id
, c.report_date_julian )
, f.organization_id
, f.request_type
, rollup( f.asset_group_id
, f.instance_id )
, rollup( f.department_id )
, rollup ( case
when f.completion_days < b.range1_high or
b.range1_high is null then 1
when f.completion_days >= b.range1_high and
(f.completion_days < b.range2_high or
b.range2_high is null) then 2
when f.completion_days >= b.range2_high and
(f.completion_days < b.range3_high or
b.range3_high is null) then 3
when f.completion_days >= b.range3_high and
(f.completion_days < b.range4_high or
b.range4_high is null) then 4
when f.completion_days >= b.range4_high and
(f.completion_days < b.range5_high or
b.range5_high is null) then 5
when f.completion_days >= b.range5_high and
(f.completion_days < b.range6_high or
b.range6_high is null) then 6
when f.completion_days >= b.range6_high and
(f.completion_days < b.range7_high or
b.range7_high is null) then 7
when f.completion_days >= b.range7_high and
(f.completion_days < b.range8_high or
b.range8_high is null) then 8
when f.completion_days >= b.range8_high and
(f.completion_days < b.range9_high or
b.range9_high is null) then 9
when f.completion_days >= b.range9_high and
(f.completion_days < b.range10_high or
b.range10_high is null) then 10
else 0
end )
[Home] [Help]