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

CREATE OR REPLACE PROCEDURE shanglifeecif.updateIndividualLable_21_40() IS
DECLARE
BEGIN
	--18	个人年收入区间	label21	"根据个人年收入字段进行判断：case PIncome when PIncome<=120000 then '低收入' when PIncome>120000 and PIncome<=1000000 then '中产' when --PIncome>1000000 then '富人' else null"	低收入、中产、富人	
	--根据数据判断单位应该是万元，这里都除以10000做判断
	UPDATE shanglifeecif.Individual si1 SET si1.Label21 = (
			CASE 
				WHEN si1.pincome >=0 AND si1.pincome<=12 THEN '低收入' 
				WHEN si1.pincome >12 AND si1.pincome<=100 THEN '中产' 
				WHEN si1.pincome > 100 THEN '富人'
				else null END 
			) where 1 = 1;
	--19	拥有车辆数量区间	label22	"根据车辆登录数量字段进行判断：case COVehicle when COVehicle=0 then '无车' when COVehicle=1 then '1辆车' when COVehicle>1 then '2辆车以上'
    --else null" 无车，1辆车，2辆车及以上	
	UPDATE shanglifeecif.Individual si1 SET si1.Label22 = (
		CASE 
		WHEN si1.COVehicle ==0  THEN '无车' 
		WHEN si1.COVehicle == 1  THEN '1辆车' 
		WHEN si1.COVehicle > 1  THEN '2辆车以上' 
		ELSE NULL END 
		) where 1 = 1;	
	--20	拥有住房套数	label23	"根据房屋登录数量字段进行判断：case CORP when CORP=0 then '无房' when CORP=1 then '1套房' when CORP>1 then '2套房以上'
	--else null"	无房，1套房，2套房及以上	
	UPDATE shanglifeecif.Individual si1 SET si1.Label23 = (
		CASE 
		WHEN si1.CORP ==0  THEN '无房' 
		WHEN si1.CORP == 1  THEN '1套房' 
		WHEN si1.CORP > 1  THEN '2套房以上' 
		else null END 
		) where 1 = 1;
	--21	是否持有寿险有效保单	label24	筛选客户名下所有保单，保单中客户为投保人，保单险种大类为寿险	持有寿险有效保单	
	UPDATE shanglifeecif.Individual si1 SET si1.label24 = '持有寿险有效保单' WHERE si1.scustid in
	(SELECT p.customerno FROM policy_information p,lmriskapp l WHERE p.riskcode = l.riskcode AND p.appflag = '有效' AND l.kindcode = 'L');
	--22	保单件数区间	label25	"判断客户作为投保人所有的保单数量(有效保单数量+失效保单数量）：NOVPolicy+NOIVPolicy=pCount(保单总件数)
	--case pCount when pCount=1 then '1件' when pCount=2 then '2件' when pCount>2 and pCount<6 then '3-5件' when pCount>5 and pCount<11 then '6-10件' 
	--when pCount>10 and pCount<21 then '10-20件'when pCount>20 and pCount<51 then '21-50件'
	--when pCount>50 then '50件以上' else '无保单'"	无保单、1件保单、2件保单、3-5件保单、6-10件保单、11-20件保单、20-50件保单、50件以上保单	
	--第一步 更新 Individual.NOVPolicy 有效记录数
	UPDATE shanglifeecif.Individual si1 SET si1.NOVPolicy = (SELECT nvl(t.cnum,0) FROM (
			SELECT p.customerno,count(*) AS cnum FROM policy_information p WHERE p.appflag = '有效' GROUP BY p.customerno 
	) t WHERE t.customerno =  si1.scustid); 
	
	--第二步 更新 Individual.NOIVPolicy 无效记录数
	UPDATE shanglifeecif.Individual si1 SET si1.noivpolicy = (SELECT nvl(t.cnum,0) FROM (
			SELECT p.customerno,count(*) AS cnum FROM policy_information p WHERE p.appflag <> '有效' GROUP BY p.customerno 
	) t WHERE t.customerno =  si1.scustid); 
	
	--第三步
	--UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (
		--CASE 
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 0 THEN '无保单' 
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 1 THEN '1件' 
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 2 THEN '2件'  
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 3  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 5 THEN '3-5件'  
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 6  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 10 THEN '6-10件'  
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 11  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0))<= 20 THEN '11-20件'  
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 21  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 50 THEN '21-50件'  
				--WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) > 50 THEN '50件以上' 
		--END ) where 1 = 1;
		
	UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (
		CASE 
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 0 THEN '无保单' 
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 1 THEN '1件保单' 
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 2 THEN '2件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 3  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 5 THEN '3-5件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 6  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 10 THEN '6-10件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 11  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0))<= 20 THEN '11-20件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 21  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 50 THEN '21-50件保单'  
				WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) > 50 THEN '50件保单以上' 
		END 
			) where 1 = 1;	
		
	
	--23	是否贷款	label26	筛选客户为投保人的有效保单，且办理了贷款业务	有贷款	
	--24	是否有极短意保单/是否仅有极短意保单	 label27	"客户为保单投保人 case AGRMNTAge(保险期限) when avg(sum(AGRMNTAges))<90 then '仅极短意外险'
			--when AGRMNTAge<90  then '有极短意外险'"	仅极短意外险，有极短意外险	志广说只用判断有极短意外险不用判断仅有
		UPDATE shanglifeecif.Individual si1 SET si1.Label27 = (
			SELECT
				CASE	
					WHEN temp.cnt >0 THEN
							'有极短意外险' 	
				END
			FROM
				(
				SELECT
					count(1) cnt,
					policy.customerno 
				FROM
					policy_information policy
				where
					policy.SECURITY in('7天','15天','30天','90天','1月','3月')
				and
					policy.riskperiod='短期险'
				and	
					policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')
				
				GROUP BY
					policy.customerno 
				) temp 
			WHERE temp.customerno = si1.scustid 
		);
	--29	是否有趸交保单	Label29	有趸交保单	NOSPInsurance，POLICY_INFORMATION.PAYINTV='趸缴',PREMIUM_LIST.PAYMENT_PERIOD='一次交清'	
	UPDATE shanglifeecif.Individual si1 SET si1.Label29 = '有趸交保单' WHERE si1.scustid IN (
		SELECT p.customerno FROM policy_information p WHERE p.payintv = '趸缴' AND p.contno IN (SELECT l.contno FROM PREMIUM_LIST l WHERE l.PAYMENT_PERIOD LIKE '%一次交清 %')	
	);	
	--30	是否给本人投保	Label30	仅本人投保，本人已投保，本人未投保	Iself	
	UPDATE shanglifeecif.Individual si1 SET si1.Label30 = (
	
		SELECT 
			CASE 
				WHEN tt.bnum = 0  THEN '本人未投保'
				WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '本人已投保'
				WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅本人投保'
			END
			
		FROM (
			SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
				SELECT count(*) AS bnum,p.customerno FROM policy_information p WHERE p.relationtoappnt = '本人' GROUP BY p.customerno
			) t LEFT JOIN policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
		) tt WHERE tt.customerno = si1.scustid
	
	);
	--32	是否给父母投保	Label31	仅父母投保，父母已投保，父母未投保	Iparent	
	UPDATE shanglifeecif.Individual si1 SET si1.Label31 = (
	
		SELECT 
			CASE 
				WHEN tt.bnum = 0  THEN '父母未投保'
				WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '父母已投保'
				WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅父母投保'
			END
			
		FROM (
			SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
				SELECT count(*) AS bnum,p.customerno FROM policy_information p WHERE p.relationtoappnt = '父母' GROUP BY p.customerno
			) t LEFT JOIN policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
		) tt WHERE tt.customerno = si1.scustid
	
	);
	--34	是否给子女投保	Label32	仅子女投保，子女已投保，子女未·投保	Ichildren	
	UPDATE shanglifeecif.Individual si1 SET si1.Label32 = (
	
		SELECT 
			CASE 
				WHEN tt.bnum = 0  THEN '子女未投保'
				WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '子女已投保'
				WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅子女投保'
			END
			
		FROM (
			SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
				SELECT count(*) AS bnum,p.customerno FROM policy_information p WHERE p.relationtoappnt = '子女' GROUP BY p.customerno
			) t LEFT JOIN policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
		) tt WHERE tt.customerno = si1.scustid
	);
	
		
	--36	是否给配偶投保	Label33	仅配偶投保，配偶已投保，配偶未投保	Imate	
	UPDATE shanglifeecif.Individual si1 SET si1.Label33 = (
	
		SELECT 
			CASE 
				WHEN tt.bnum = 0  THEN '配偶未投保'
				WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '配偶已投保'
				WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅配偶投保'
			END
			
		FROM (
			SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
				SELECT count(*) AS bnum,p.customerno FROM policy_information p WHERE p.relationtoappnt = '配偶' GROUP BY p.customerno
			) t LEFT JOIN policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
		) tt WHERE tt.customerno = si1.scustid
	);	
	
	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