513 lines
22 KiB
SQL
513 lines
22 KiB
SQL
--execute clean_mensual_renewal;
|
||
INSERT INTO desktop_archievement_admin.坐席续保统计
|
||
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.坐席名称,
|
||
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(to_char(SYSDATE,
|
||
'yyyy-mm') || '-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.坐席名称,
|
||
aa.坐席工号)
|
||
--个车续保率=已续数-累计/到期数-累计
|
||
,
|
||
dqs AS
|
||
(
|
||
---到期数
|
||
SELECT aa.责任部门 部门,
|
||
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(to_char(SYSDATE,
|
||
'yyyy-mm') || '-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.坐席名称,
|
||
aa.坐席工号)
|
||
SELECT dqs.部门,
|
||
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.坐席名称
|
||
AND dqs.坐席工号 = yxb.坐席工号;
|
||
COMMIT;
|