SELECT /* Dummy Comment */
(CASE
WHEN GROUPING(b.report_date_julian)=0 THEN b.report_date_julian
WHEN GROUPING(b.week_id)=0 THEN b.week_id
WHEN GROUPING(b.ent_period_id)=0 THEN b.ent_period_id
WHEN GROUPING(b.ent_qtr_id)=0 THEN b.ent_qtr_id
ELSE b.ent_year_id
END
) time_id,
(CASE
WHEN GROUPING(b.report_date_julian)=0 THEN 1
WHEN GROUPING(b.week_id)=0 THEN 16
WHEN GROUPING(b.ent_period_id)=0 THEN 32
WHEN GROUPING(b.ent_qtr_id)=0 THEN 64
ELSE 128
END
) period_type_id
,segment_id
,sum(acquired_customers) acquired_customers
,sum(total_customers) total_customers
,sum(lost_customers) lost_customers
,sum(segment_size) segment_size
,sum(booked_amt) booked_amt
,sum(booked_amt_s) booked_amt_s
,sum(booked_Count) booked_Count
,sum(revenue) revenue
,sum(revenue_s) revenue_s
,count(acquired_customers) acquired_customers_c
,count(total_customers) total_customers_c
,count(lost_customers) lost_customers_c
,count(segment_size) segment_size_c
,count(booked_amt) booked_amt_c
,count(booked_amt_s) booked_amt_s_c
,count(booked_Count) booked_Count_c
,count(revenue) revenue_c
,count(revenue_s) revenue_s_c
,b.report_date_julian dummy1_fr
,b.week_id dummy2_fr
,b.ent_period_id dummy3_fr
,b.ent_qtr_id dummy4_fr
,b.ent_year_id dummy5_fr
,grouping_id(b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id,segment_id) dummy6_fr
,count(*) all_c
FROM bim_sgmt_val_b_mv a , FII.FII_TIME_DAY b
WHERE b.report_date = a.transaction_create_date
GROUP BY segment_id, GROUPING SETS (b.report_date_julian,b.week_id,b.ent_period_id,b.ent_qtr_id,b.ent_year_id)
[Home] [Help]