drop table if exists ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp2;
create table if not exists ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp2 stored as parquet as
select
max(t1.assess_type) as assess_type
,max(t1.target_id_head) as target_id_head
,max(t1.target_id) as target_id
,max( target_id_path ) as target_id_path
,max( t1.push_target_child_id) as push_target_child_id
,t1.sub_target_id
,max(t1.target_type) as target_type
,max(t1.y) as y
,max(t1.m) as m
,max(t1.start_date) as start_date
,max(t1.end_date) as end_date
,max(t1.target_name) as target_name
,max(t1.decompose_range ) as decompose_range
,max(t1.rang_shop_type ) as rang_shop_type
,max(t1.rang_shop_level ) as rang_shop_level
,max(t1.rang_shop_label ) as rang_shop_label
,max(t1.rang_is_have_guide) as rang_is_have_guide
,max(t1.rang_shop_type_desc ) as rang_shop_type_desc
,max(t1.rang_shop_level_desc ) as rang_shop_level_desc
,max(t1.rang_shop_label_desc ) as rang_shop_label_desc
,max(t1.rang_is_have_guide_desc) as rang_is_have_guide_desc
,max(t1.decompose) as decompose
,max(t1.target_organization) as target_organization
,max(t1.target_organization_name) as target_organization_name
,max(t1.target_organization_level) as target_organization_level
,max(t1.child_target_name) as child_target_name
,max(t1.dimension) as dimension
,max(t1.product) as product
,max(t1.product_type) as product_type
,max(t1.product_code) as product_code
,max(t1.product_name) as product_name
,max(t1.statistics_rule) as statistics_rule
,max(t1.target_status) as target_status
,max(t1.target_delivery_level) as target_delivery_level
,max(t1.sales_org_code_l4) as sales_org_code_l4
,max(t1.sales_org_name_l4) as sales_org_name_l4
,max(t1.staff_code_l4) as staff_code_l4
,max(t1.staff_name_l4) as staff_name_l4
,max(t1.sales_org_code_l5) as sales_org_code_l5
,max(t1.sales_org_name_l5) as sales_org_name_l5
,max(t1.staff_code_l5) as staff_code_l5
,max(t1.staff_name_l5) as staff_name_l5
,max(t1.sales_org_code_l6) as sales_org_code_l6
,max(t1.sales_org_name_l6) as sales_org_name_l6
,max(t1.staff_code_l6) as staff_code_l6
,max(t1.staff_name_l6) as staff_name_l6
,max(t1.sales_org_code_l7) as sales_org_code_l7
,max(t1.sales_org_name_l7) as sales_org_name_l7
,max(t1.staff_code_l7) as staff_code_l7
,max(t1.staff_name_l7) as staff_name_l7
,max(t1.sales_org_code_l8) as sales_org_code_l8
,max(t1.sales_org_name_l8) as sales_org_name_l8
,max(t1.staff_code_l8) as staff_code_l8
,max(t1.staff_name_l8) as staff_name_l8
,max(t1.sales_org_code_l9) as sales_org_code_l9
,max(t1.sales_org_name_l9) as sales_org_name_l9
,max(t1.staff_code_l9) as staff_code_l9
,max(t1.staff_name_l9) as staff_name_l9
,max(t1.sales_org_code) as sales_org_code
,'' as cust_code_group
,'' as cust_name_group
,'' as cust_code
,'' as cust_name
,'' as shop_code
,'' as shop_name
,max(t1.target_current) as target_current
,max(t1.target_next) as target_next
,sum(
case when t1.target_type = 0 then
case when t1.statistics_rule=0
then t2.valid_report_qty
when t1.statistics_rule=1
then t2.reg_qty
when t1.statistics_rule=2
then t2.report_qty
else 0 end
when t1.target_type = 2 then
case when t1.statistics_rule=0
then t2.valid_report_qty * t2.retail_price
when t1.statistics_rule=1
then t2.reg_qty * t2.retail_price
when t1.statistics_rule=2
then t2.report_qty * t2.retail_price
else 0 end
when t1.target_type = 3 then t2.reserve_qty
else 0 end
) as cumulative_completion
,current_timestamp() as etl_time
from
${DM_CRM}.dwd_sfa_reach_goal_all_d t1
left join (
select
t1.sales_org_code_l4 as union_field
,coalesce(shop_type,-99) as shop_type
,coalesce(shop_level,-99) as shop_level
,coalesce(is_have_guide,-99) as is_have_guide
,coalesce(shop_label_ids,array()) as shop_label_ids
,t1.category_type,t1.mkt_series_code,t1.mkt_subseries_code,t1.spu_code,t1.sku_code,t1.dayno,csc_ram_model_id
,max(retail_price) as retail_price,sum(reg_qty) as reg_qty,sum(report_qty) as report_qty,sum(valid_report_qty) as valid_report_qty
,sum(reserve_qty) as reserve_qty
from ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp1 t1
group by t1.sales_org_code_l4,t1.category_type,t1.spu_code,t1.sku_code,t1.dayno,mkt_series_code,mkt_subseries_code,csc_ram_model_id
,shop_type
,shop_level
,is_have_guide
,shop_label_ids
union all
select
t1.sales_org_code_l5 as union_field
,coalesce(shop_type,-99) as shop_type
,coalesce(shop_level,-99) as shop_level
,coalesce(is_have_guide,-99) as is_have_guide
,coalesce(shop_label_ids,array()) as shop_label_ids
,t1.category_type,t1.mkt_series_code,t1.mkt_subseries_code,t1.spu_code,t1.sku_code,t1.dayno,csc_ram_model_id
,max(retail_price) as retail_price,sum(reg_qty) as reg_qty,sum(report_qty) as report_qty,sum(valid_report_qty) as valid_report_qty
,sum(reserve_qty) as reserve_qty
from ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp1 t1
group by t1.sales_org_code_l5,t1.category_type,t1.spu_code,t1.sku_code,t1.dayno,mkt_series_code,mkt_subseries_code,csc_ram_model_id
,shop_type
,shop_level
,is_have_guide
,shop_label_ids
union all
select
t1.sales_org_code_l6 as union_field
,coalesce(shop_type,-99) as shop_type
,coalesce(shop_level,-99) as shop_level
,coalesce(is_have_guide,-99) as is_have_guide
,coalesce(shop_label_ids,array()) as shop_label_ids
,t1.category_type,t1.mkt_series_code,t1.mkt_subseries_code,t1.spu_code,t1.sku_code,t1.dayno,csc_ram_model_id
,max(retail_price) as retail_price,sum(reg_qty) as reg_qty,sum(report_qty) as report_qty,sum(valid_report_qty) as valid_report_qty
,sum(reserve_qty) as reserve_qty
from ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp1 t1
group by t1.sales_org_code_l6,t1.category_type,t1.spu_code,t1.sku_code,t1.dayno,mkt_series_code,mkt_subseries_code ,csc_ram_model_id
,shop_type
,shop_level
,is_have_guide
,shop_label_ids
union all
select
t1.sales_org_code_l7 as union_field
,coalesce(shop_type,-99) as shop_type
,coalesce(shop_level,-99) as shop_level
,coalesce(is_have_guide,-99) as is_have_guide
,coalesce(shop_label_ids,array()) as shop_label_ids
,t1.category_type,t1.mkt_series_code,t1.mkt_subseries_code,t1.spu_code,t1.sku_code,t1.dayno,csc_ram_model_id
,max(retail_price) as retail_price,sum(reg_qty) as reg_qty,sum(report_qty) as report_qty,sum(valid_report_qty) as valid_report_qty
,sum(reserve_qty) as reserve_qty
from ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp1 t1
group by t1.sales_org_code_l7,t1.category_type,t1.spu_code,t1.sku_code,t1.dayno,mkt_series_code,mkt_subseries_code ,csc_ram_model_id
,shop_type
,shop_level
,is_have_guide
,shop_label_ids
union all
select
t1.sales_org_code_l8 as union_field
,coalesce(shop_type,-99) as shop_type
,coalesce(shop_level,-99) as shop_level
,coalesce(is_have_guide,-99) as is_have_guide
,coalesce(shop_label_ids,array()) as shop_label_ids
,t1.category_type,t1.mkt_series_code,t1.mkt_subseries_code,t1.spu_code,t1.sku_code,t1.dayno,csc_ram_model_id
,max(retail_price) as retail_price,sum(reg_qty) as reg_qty,sum(report_qty) as report_qty,sum(valid_report_qty) as valid_report_qty
,sum(reserve_qty) as reserve_qty
from ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp1 t1
group by t1.sales_org_code_l8,t1.category_type,t1.spu_code,t1.sku_code,t1.dayno,mkt_series_code,mkt_subseries_code ,csc_ram_model_id
,shop_type
,shop_level
,is_have_guide
,shop_label_ids
union all
select
t1.sales_org_code_l9 as union_field
,coalesce(shop_type,-99) as shop_type
,coalesce(shop_level,-99) as shop_level
,coalesce(is_have_guide,-99) as is_have_guide
,coalesce(shop_label_ids,array()) as shop_label_ids
,t1.category_type,t1.mkt_series_code,t1.mkt_subseries_code,t1.spu_code,t1.sku_code,t1.dayno,csc_ram_model_id
,max(retail_price) as retail_price,sum(reg_qty) as reg_qty,sum(report_qty) as report_qty,sum(valid_report_qty) as valid_report_qty
,sum(reserve_qty) as reserve_qty
from ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp1 t1
group by t1.sales_org_code_l9,t1.category_type,t1.spu_code,t1.sku_code,t1.dayno,mkt_series_code,mkt_subseries_code ,csc_ram_model_id
,shop_type
,shop_level
,is_have_guide
,shop_label_ids
union all
select
t1.sales_org_code_l10 as union_field
,coalesce(shop_type,-99) as shop_type
,coalesce(shop_level,-99) as shop_level
,coalesce(is_have_guide,-99) as is_have_guide
,coalesce(shop_label_ids,array()) as shop_label_ids
,t1.category_type,t1.mkt_series_code,t1.mkt_subseries_code,t1.spu_code,t1.sku_code,t1.dayno,csc_ram_model_id
,max(retail_price) as retail_price,sum(reg_qty) as reg_qty,sum(report_qty) as report_qty,sum(valid_report_qty) as valid_report_qty
,sum(reserve_qty) as reserve_qty
from ${DM_CRM_TMP}.rpt_crm_reach_goal_shop_all_d_tmp1 t1
group by t1.sales_org_code_l10,t1.category_type,t1.spu_code,t1.sku_code,t1.dayno,mkt_series_code,mkt_subseries_code ,csc_ram_model_id
,shop_type
,shop_level
,is_have_guide
,shop_label_ids
) t2
on 1=1
and cast(t2.dayno as string) between regexp_replace(t1.start_date,'-','') and regexp_replace(t1.end_date,'-','')
and t1.sales_org_code = union_field
and case when t1.decompose_range = 2 and t1.shop_code = '' then
if(size(t1.rang_shop_type)>0 , array_contains(t1.rang_shop_type, t2.shop_type),true )
and if(size(t1.rang_shop_level) >0 ,array_contains(t1.rang_shop_level, t2.shop_level) ,true )
and if( size(t1.rang_shop_label) > 0 ,arrays_overlap(t1.rang_shop_label, t2.shop_label_ids) ,true)
and if( size(t1.rang_is_have_guide ) > 0 , array_contains(t1.rang_is_have_guide, t2.is_have_guide) ,true)
else true end
and (
t1.dimension = 0
and (t1.product + 1 = t2.category_type or t1.product = 2)
or (
t1.dimension=1
and (
(
t1.product_type = 1
and array_contains(t1.product_code, t2.spu_code)
)
or (
t1.product_type = 2
and array_contains(t1.product_code, t2.sku_code)
)
or (
t1.product_type = 3
and array_contains(t1.product_code,t2.mkt_series_code)
)
or (
t1.product_type = 4
and array_contains(t1.product_code,t2.mkt_subseries_code)
)
or (
t1.product_type = 5
and array_contains(t1.product_code,t2.csc_ram_model_id)
)
)
)
)
where
t1.dayno = ${v_day}
and t1.decompose in (0)
and coalesce(t1.shop_code,t1.staff_code,'') = ''
group by sub_target_id
