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 |
