insert overwrite table INTF.TT_TM_PTY_CHECK_DAY
SELECT
a.pd_inst_id
,a.cust_id
,a.pd_spec_id
,CASE WHEN a.PD_SPEC_ID IN (9, 10, 11, 12, 14, 348, 354, 383) THEN 'BRD'
WHEN a.PD_SPEC_ID IN (379, 901, 804, 805) THEN 'MBL'
WHEN a.PD_SPEC_ID IN (881) THEN 'ITV'
ELSE 'OTH' END
,a.busi_st
,a.busi_st_dt
,a.blng_accs_nmbr
,INT(REGEXP_REPLACE(a.start_dt,'-','')) start_dt
,INT(REGEXP_REPLACE(a.end_dt,'-','')) end_dt
,a.blng_user_f
,a.online_f
,a.comp_f
,a.exist_tp_id
,a.chrg_f
,a.last_chrg_f
,d.city_cd
,d.city_nm
,a.agent_chnl_id
,m.agent_chnl_nm
,b.bss_po_spec_id
,b.bss_po_spec_nm
,a.ASSIST_CD
,INT(REGEXP_REPLACE(a.crtd_dt,'-','')) crtd_dt
,a.BLNG_MODE
FROM (select * from bml.pd_inst_daily_info WHERE clndr_dt_id ='${hiveconf:START_TIME}') a
left outer join ( select * from bml.bss_po_spec_d gg,
(select max(int(regexp_replace(l.end_dt,'-',''))) end_dt2,l.po_spec_id po_spec_id2
from bml.bss_po_spec_d l
group by l.po_spec_id) dd
where int(regexp_replace(gg.end_dt,'-','')) =dd.end_dt2 and gg.po_spec_id=dd.po_spec_id2
) b on a.main_po_spec_id = b.po_spec_id
left outer join bml.dim_city_mpng d on a.city_id = d.city_id
inner join (select agent_chnl_id ,city_id,agent_chnl_nm
from bml.agent_chnl_d_all
where (agent_chnl_nm like '%省电渠人工业务受理%'
or agent_chnl_nm like '%省电渠苏宁易购业务受理%'
or agent_chnl_nm like'%省电渠京东商城业务受理%'
or agent_chnl_nm like '%省电渠网翼分销渠道%'
or agent_chnl_no like '%省电渠天猫业务受理%' )
and clndr_dt_id = '${hiveconf:START_MONTH}'
) m
on a.agent_chnl_id = m.agent_chnl_id
;
0 个回复