| WITH ztyx AS ( select
 data_dt,
 branch,
 sec_branch_name as fh,
 appdec_day,
 newac_flag,
 case
 when assit_camp_branch_num = '' then 1
 else 0
 end as tzlx
 from
 dm.d_tgb_receive_app_dtl_xyk0036
 where
 newac_flag = '1'
 and assit_camp_branch_num = ''
 ),
 xxyx as (
 select
 data_dt,
 d.sec_branch_name as fh,
 assit_camp_branch_num as branch,
 appdec_day,
 newac_flag,
 case
 when d.sec_branch_name in (
 '北京分行',
 '中关村分行',
 '城市副中心分行',
 '总行营业部',
 '上海分行',
 '天津分行',
 '苏州分行',
 '宁波分行',
 '南京分行',
 '石家庄分行',
 '西安分行',
 '长沙分行',
 '济南分行',
 '青岛分行',
 '深圳分行',
 '乌鲁木齐分行',
 '南昌分行',
 '杭州分行'
 ) then 2
 else 3
 end as tzlx
 from
 dm.d_tgb_receive_app_dtl_xyk0036 xyk
 left join dm.d_branch d on assit_camp_branch_num = org_code
 where
 newac_flag = '1'
 and assit_camp_branch_num <> ''
 and is_valid = '1'
 ),
 xxhb as(
 select
 data_dt,
 branch,
 fh,
 appdec_day,
 newac_flag,
 tzlx
 from
 ztyx
 union all
 select
 data_dt,
 branch,
 fh,
 appdec_day,
 newac_flag,
 tzlx
 from
 xxyx
 )
 select
 fh,
 sum(
 case
 when tzlx = '1' then 1
 when tzlx = '3' then 0.75
 else tzlx
 end
 )
 from
 xxhb
 where
 data_dt = '2024-11-13'
 and appdec_day >= '2024-11-10'
 group by
 fh
 |