184 lines
12 KiB
SQL
184 lines
12 KiB
SQL
with cc as(
|
||
select pt.policy_no policy_no,sum(pt.premium_amount )premium_amount,sum(pt.tax_amount) tax_amount from idst0.auto_premium_t pt group by pt.policy_no
|
||
)
|
||
|
||
|
||
,bb as (select
|
||
t.policy_no 保单号,
|
||
(case
|
||
when e.product_code like '1102%' and e.product_code not like '110224%' and e.product_code !='11026000'then '机动车险保险'
|
||
when e.product_code like '1101%' or e.product_code like '1301%' or e.product_code like '11110000%' then '财产险'
|
||
when e.product_code like '1107%' or e.product_code like '1307%' or e.product_code like '1304A400%' then '责任险'
|
||
when e.product_code like '1106%' or e.product_code like '51015700' then '家财险'
|
||
when (e.product_code like '1104%' or e.product_code like '1204%' or e.product_code like '13040000%') and e.product_code not like '120404%' then '货运险'
|
||
when e.product_code like '1203%' or e.product_code like '1103%' or e.product_code like '120404%' then '船舶险'
|
||
when e.product_code like '1305%' then '工程险'
|
||
when e.product_code like '111%' and e.product_code not like '11110000%' then '农业险'
|
||
when e.product_code like '1109%' or e.product_code like '1309%' then '保证保险'
|
||
when e.product_code like '1312%' then '特殊风险'
|
||
when e.product_code like '1308%' then '信用险'
|
||
when e.product_code like '23%' then '意外险'
|
||
when e.product_code like '22%' then '健康险'
|
||
when e.product_code like '110224%'or e.product_code='11026000' then '交强险'
|
||
else '未分配' end) as 险种大类
|
||
from
|
||
idst0.auto_agreement_request_t t
|
||
left join idst0.auto_agreement_t e on e.policy_no = t.policy_no
|
||
)
|
||
|
||
, aa1 as (select
|
||
a.policy_no 旧保单
|
||
,a.selling_shop_code 车商代码
|
||
,a.planned_end_date 到期时间
|
||
,case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '个车专业化销售一科'
|
||
when a.planned_end_date > to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and (a.selling_shop_code||a.tel_sale_4s_code IN ('00EA','00F1','00G9')) then '个车专业化销售一科'
|
||
else nvl(dt.科室名称,z.section_office_name) end 科室
|
||
,(case when to_number(to_char(a.planned_end_date,'yyyy'))='2023' and nvl(dt.部门,y.department_name) not like '湖里支公司' and（cd.memo like '%深圳%' or cd.memo like '%地面%' ）and v.vehicle_brand in ('特斯拉','蔚来','理想','小鹏','极氪','极狐','AITO','ARCFOX极狐', '特斯拉（中国）', '特斯拉(中国)') AND v.vin not in ('LJ1E6A2UXL7744108','LW433B10XL1001411','LJ1E6A3U2L7742089','L1NSPGHB0MA002652') THEN '新能源车事业发展中心厦门分中心'
|
||
when (case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '集美支公司' else nvl(dt.部门,y.department_name) end)='湖里支公司'
|
||
then (case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '集美支公司' else nvl(dt.部门,y.department_name) end )
|
||
when （cd.memo like '%地面%'or cd.memo like '%深圳%'） then '续保业务部'
|
||
when a.planned_end_date > to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and (a.selling_shop_code||a.tel_sale_4s_code IN ('00EA','00F1','00G9')) then '同安支公司'
|
||
else (case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '集美支公司' else nvl(dt.部门,y.department_name) end) end) 责任部门
|
||
,qa.USAGE_XM 使用性质
|
||
,decode(qa.bd_type,'1','单交强','2','单商业','3','交商共保','其它') 保单类型
|
||
,(case when (qa.bd_type='1' and nt.ecompensation_rate>1) then '是' end ) 高预赔
|
||
,x.staff_name as 经办
|
||
,bb.险种大类
|
||
,cc.signature_date 新保单签发时间
|
||
,(select hmd.管控类型 from (select distinct * from dc_lsj_xb_hmd) hmd where hmd.vin=v.vin) 管控类型
|
||
|
||
from
|
||
idst0.auto_agreement_t a
|
||
left join bb on bb.保单号=a.policy_no
|
||
left join ywglxt.w_dxbd_i i on a.policy_no = i.bdh
|
||
left join idst0.motorised_vehicle_t v on a.policy_no=v.policy_no
|
||
left join ywglxt.q_auto_agreement_t qa on qa.policy_no=a.policy_no
|
||
|
||
left join idst0.auto_new_product_info_t nt on nt.policy_no=a.policy_no and nt.endorsement_no='无'
|
||
left join idst0.rydm_t x on x.staff_code = nvl(upper(i.zhjywy),a.operator_code)
|
||
left join idst0.bm_t y on x.department_code = y.department_code
|
||
left join idst0.ks_t z on x.section_office_code = z.section_office_code
|
||
left join cc pt on pt.policy_no=a.policy_no
|
||
left join
|
||
(select distinct aa.policy_no,
|
||
vv.vin,aa.inception_date,t.signature_date,(pt.premium_amount - nvl(pt.tax_amount,0)) qdbf,
|
||
(case when aa.inception_date - aa.issue_date >= '30' then vv.vin end) if30 ,bb.险种大类
|
||
from idst0.auto_agreement_t aa
|
||
left join bb on bb.保单号=aa.policy_no
|
||
left join idst0.motorised_vehicle_t vv on vv.policy_no = aa.policy_no
|
||
left join idst0.auto_agreement_request_t t on t.policy_no = aa.policy_no and t.endorsement_no = '无'
|
||
left join idst0.auto_premium_t pt on pt.policy_no = aa.policy_no and pt.endorsement_no = '无'
|
||
where aa.policy_no=vv.policy_no and aa.policy_status = '1' --and aa.product_code not in ('11022400','11023900','11024000','11024600','11026000')
|
||
and aa.branch_company_code = '3080100'and (pt.premium_amount - nvl(pt.tax_amount,0))>100
|
||
and aa.inception_date >= to_date('2022-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
||
and aa.inception_date < to_date('2023-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
||
and aa.branch_company_code = vv.branch_company_code
|
||
) cc on (cc.vin = v.vin and cc.险种大类=bb.险种大类) and cc.inception_date > a.planned_end_date-30 --时间限制放宽为30天
|
||
left join dc_yangg_cx_dianxiaodoudi cd on cd.policy_no = a.policy_no
|
||
left join dc_tb_jc_c_tag dt on dt.保单号=a.policy_no
|
||
left join idst0.auto_agreement_t aa on aa.policy_no= cc.policy_no
|
||
left join cc pt1 on pt1.policy_no=aa.policy_no
|
||
where a.policy_status = '1' and a.planned_end_date-a.inception_date>270 and
|
||
a.planned_end_date >= to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
||
and a.planned_end_date < to_date('2023-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
|
||
, aa2 as (select
|
||
a.policy_no 旧保单
|
||
,a.selling_shop_code 车商代码
|
||
,a.planned_end_date 到期时间
|
||
,case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '个车专业化销售一科'
|
||
when a.planned_end_date > to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and (a.selling_shop_code||a.tel_sale_4s_code IN ('00EA','00F1','00G9')) then '个车专业化销售一科'
|
||
else nvl(dt.科室名称,z.section_office_name) end 科室
|
||
,(case when to_number(to_char(a.planned_end_date,'yyyy'))='2023' and nvl(dt.部门,y.department_name) not like '湖里支公司' and（cd.memo like '%深圳%' or cd.memo like '%地面%' ）and v.vehicle_brand in ('特斯拉','蔚来','理想','小鹏','极氪','极狐','AITO','ARCFOX极狐', '特斯拉（中国）', '特斯拉(中国)') AND v.vin not in ('LJ1E6A2UXL7744108','LW433B10XL1001411','LJ1E6A3U2L7742089','L1NSPGHB0MA002652') THEN '新能源车事业发展中心厦门分中心'
|
||
when (case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '集美支公司' else nvl(dt.部门,y.department_name) end)='湖里支公司'
|
||
then (case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '集美支公司' else nvl(dt.部门,y.department_name) end )
|
||
when （cd.memo like '%地面%'or cd.memo like '%深圳%'） then '续保业务部'
|
||
when a.planned_end_date > to_date('2023-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and (a.selling_shop_code||a.tel_sale_4s_code IN ('00EA','00F1','00G9')) then '同安支公司'
|
||
else (case when to_number(to_char(a.planned_end_date,'yyyy'))='2022' and a.selling_shop_code||a.tel_sale_4s_code='00GK' then '集美支公司' else nvl(dt.部门,y.department_name) end) end) 责任部门
|
||
,qa.USAGE_XM 使用性质
|
||
,decode(qa.bd_type,'1','单交强','2','单商业','3','交商共保','其它') 保单类型
|
||
,(case when (qa.bd_type='1' and nt.ecompensation_rate>1) then '是' end ) 高预赔
|
||
,x.staff_name as 经办
|
||
,bb.险种大类
|
||
,cc.signature_date 新保单签发时间
|
||
,(select hmd.管控类型 from (select distinct * from dc_lsj_xb_hmd) hmd where hmd.vin=v.vin) 管控类型
|
||
|
||
from
|
||
idst0.auto_agreement_t a
|
||
left join bb on bb.保单号=a.policy_no
|
||
left join ywglxt.w_dxbd_i i on a.policy_no = i.bdh
|
||
left join idst0.motorised_vehicle_t v on a.policy_no=v.policy_no
|
||
left join ywglxt.q_auto_agreement_t qa on qa.policy_no=a.policy_no
|
||
left join idst0.auto_new_product_info_t nt on nt.policy_no=a.policy_no and nt.endorsement_no='无'
|
||
left join idst0.rydm_t x on x.staff_code = nvl(upper(i.zhjywy),a.operator_code)
|
||
left join idst0.bm_t y on x.department_code = y.department_code
|
||
left join idst0.ks_t z on x.section_office_code = z.section_office_code
|
||
left join cc pt on pt.policy_no=a.policy_no
|
||
left join
|
||
(select distinct aa.policy_no,
|
||
vv.vin,aa.inception_date,t.signature_date,(pt.premium_amount - nvl(pt.tax_amount,0)) qdbf,
|
||
(case when aa.inception_date - aa.issue_date >= '30' then vv.vin end) if30 ,bb.险种大类
|
||
from idst0.auto_agreement_t aa
|
||
left join bb on bb.保单号=aa.policy_no
|
||
left join idst0.motorised_vehicle_t vv on vv.policy_no = aa.policy_no
|
||
left join idst0.auto_agreement_request_t t on t.policy_no = aa.policy_no and t.endorsement_no = '无'
|
||
left join idst0.auto_premium_t pt on pt.policy_no = aa.policy_no and pt.endorsement_no = '无'
|
||
where aa.policy_no=vv.policy_no and aa.policy_status = '1' --and aa.product_code not in ('11022400','11023900','11024000','11024600','11026000')
|
||
and aa.branch_company_code = '3080100'and (pt.premium_amount - nvl(pt.tax_amount,0))>100
|
||
and aa.inception_date >= to_date('2022-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
||
and aa.inception_date < to_date('2023-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
||
and aa.branch_company_code = vv.branch_company_code
|
||
) cc on (cc.vin = v.vin and cc.险种大类=bb.险种大类) and cc.inception_date > a.planned_end_date-30 --时间限制放宽为30天
|
||
left join dc_yangg_cx_dianxiaodoudi cd on cd.policy_no = a.policy_no
|
||
left join dc_tb_jc_c_tag dt on dt.保单号=a.policy_no
|
||
left join idst0.auto_agreement_t aa on aa.policy_no= cc.policy_no
|
||
left join cc pt1 on pt1.policy_no=aa.policy_no
|
||
where a.policy_status = '1' and a.planned_end_date-a.inception_date>270 and
|
||
a.planned_end_date >= to_date('2023-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
|
||
and a.planned_end_date < to_date('2024-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
|
||
|
||
,aa3 as (select distinct * from aa1
|
||
union all
|
||
(select distinct * from aa2) )
|
||
|
||
,AA AS (SELECT AA3.*
|
||
,nvl（zx.workerno,et.telpartnercode) 坐席工号
|
||
,(case when nvl（zx.workerno,et.telpartnercode) = 'DX001' and aa3.经办 like '%建发凯迪%' then '林伟华'
|
||
when nvl（zx.workerno,et.telpartnercode) = 'DX001' and aa3.经办 not like '%建发凯迪%' then '其他'
|
||
else to_char(ys.坐席姓名) END) 坐席名称
|
||
,YS.团队 坐席团队
|
||
FROM AA3
|
||
left join idst0.auto_agreement_extend_t et on et.policy_no = AA3.旧保单
|
||
left join idst0.t_sell_policy_autobase_t zx on zx.policy_no = AA3.旧保单
|
||
left join dc_YZH_ZXYS YS ON YS.坐席工号= nvl（zx.workerno,et.telpartnercode))
|
||
|
||
, yxb as(
|
||
---已续保
|
||
select
|
||
aa.责任部门 部门
|
||
,aa.科室,aa.经办,aa.坐席团队,aa.坐席名称,
|
||
--(case when a.标识='非首续'and A.责任部门='续保业务部' then '续保业务部' else A.部门 end) 部门,
|
||
(case when count(1) is null then 0 else count(1) end) 已续保累计
|
||
from aa
|
||
where
|
||
aa.使用性质 in ('家庭自用车','企业客车') and aa.高预赔 is null and aa.管控类型 is null and aa.保单类型 in （'交商共保','单交强'）
|
||
and aa.到期时间 >=to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND aa.到期时间<to_date(to_char(sysdate-1,'yyyy-mm-dd')||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
|
||
AND aa.新保单签发时间<=to_date(to_char(sysdate-1,'yyyy-mm-dd')||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
|
||
group by aa.责任部门,aa.科室,aa.经办,aa.坐席团队,aa.坐席名称)
|
||
--个车续保率=已续数-累计/到期数-累计
|
||
,dqs AS(
|
||
---到期数
|
||
select
|
||
aa.责任部门 部门
|
||
,aa.科室,aa.经办,aa.坐席团队,aa.坐席名称,
|
||
(case when count(1) is null then 0 else count(1) end) 到期数
|
||
from aa
|
||
where
|
||
aa.使用性质 in ('家庭自用车','企业客车') and aa.高预赔 is null and aa.管控类型 is null and aa.保单类型 in （'交商共保','单交强'）
|
||
and aa.到期时间 >=to_date('2023-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND aa.到期时间<to_date(to_char(sysdate-1,'yyyy-mm-dd')||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
|
||
group by aa.责任部门,aa.科室,aa.经办,aa.坐席团队,aa.坐席名称
|
||
)
|
||
select dqs.部门,dqs.科室,dqs.经办,dqs.坐席团队,dqs.坐席名称,yxb.已续保累计,dqs.到期数
|
||
from yxb,
|
||
dqs--,sdqs,syxs,cyxs,cdqs
|
||
where dqs.部门= yxb.部门 AND dqs.经办= yxb.经办 AND dqs.科室= yxb.科室 AND dqs.坐席名称= yxb.坐席名称
|