ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/客户标签label81-100.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.updateIndividualLable_81_100() IS
DECLARE
BEGIN
	
	--93	失效客户	Label91	失效客户	所有保单都是失效状态	客户名下所有保单均失效的客户
	UPDATE shanglifeecif.Individual si1 SET si1.Label91 = '失效客户' WHERE si1.scustid IN (SELECT temp.customerno FROM (
	SELECT count(*) AS tnum,max(t.customerno) AS customerno,max(t.pnum) AS pnum FROM (
	SELECT p.customerno,count(*) AS pnum FROM  policy_information p WHERE p.appflag <> '有效' GROUP BY p.customerno 
	) t LEFT JOIN policy_information pi ON pi.customerno = t.customerno
	GROUP BY pi.customerno
	) temp WHERE temp.tnum = temp.pnum);
	
	
	--98	大龄	Label96	大龄 来电客户年龄50岁以上
	UPDATE shanglifeecif.Individual si1 SET si1.Label96 = (CASE WHEN TRUNC(months_between(sysdate, si1.birthday)/12)>=50 AND TRUNC(months_between(sysdate, si1.birthday)/12)<60 THEN '大龄' END ) where 1 = 1;
	--99	高龄	Label97	高龄		来电客户年龄60岁以上
	UPDATE shanglifeecif.Individual si1 SET si1.Label97 = (CASE WHEN TRUNC(months_between(sysdate, si1.birthday)/12)>=60  THEN '高龄' END) where 1 = 1;
	--100	敏感职业	Label98	敏感职业		投保人职业为记者、律师、公务员、媒体、金融行业
	UPDATE shanglifeecif.Individual si1 SET si1.Label98 = (CASE WHEN si1.occupation like '%记者%' OR si1.occupation like '%律师%' OR si1.occupation like '%公务员%' OR  si1.occupation like '%媒体%' OR  si1.occupation like '%金融行业%' THEN '敏感职业' END ) WHERE 1 = 1;
    --102	公司销售人员	Label100	公司销售人员	姓名与保单经办人一致	客户为公司销售人员
	
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());	
END