set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[SP_HP_TestGradeCustomerMonthly]

AS
BEGIN
	SET NOCOUNT ON;

--=====================================================================================

declare @APDPVD varchar(10)
select  @APDPVD = APDPVD from TMP_DBMAPD00 where APDBRN = '01' 

if( SUBSTRING(@APDPVD,7,2)='02') BEGIN


declare @InputMonth INT ;
set @InputMonth =3; --เอาไว้ทดสอบ

declare @FirstPVDMonth varchar(10)
declare @LastPVDMonth varchar(10)
set @FirstPVDMonth =convert(varchar(10),DATEADD(mm, DATEDIFF(mm, 0, DATEADD(month, -1, GETDATE())), 0),112);
set @LastPVDMonth =convert(varchar(10),DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) , 0)),112);

declare @CurrentDate varchar(10)
set @CurrentDate = convert(varchar(10),getdate(),112)
drop table #TempINS01;
select INSCRN, CONSTS, INSITM, INSDUE , INSILD 
,(case  when (INSDUE is null)or(INSDUE = 0) then 0 
        --จ่ายมาครบปกติ
        when not(INSDUE is null) and not(INSDUE = 0) and not(INSILD is null) and not(INSILD = 0) then 
           (DATEDIFF(day, (convert(datetime,SUBSTRING(cast(INSDUE as varchar(10)),1,4)+'-'+SUBSTRING(cast(INSDUE as varchar(10)),5,2)+'-'+SUBSTRING(cast(INSDUE as varchar(10)),7,2))) , 
                   (convert(datetime,SUBSTRING(cast(INSILD as varchar(10)),1,4)+'-'+SUBSTRING(cast(INSILD as varchar(10)),5,2)+'-'+SUBSTRING(cast(INSILD as varchar(10)),7,2))) ))
        --จ่ายมาบางส่วน
        when not(INSDUE is null) and not(INSDUE = 0) and ((INSILD is null) or (INSILD = 0)) and (INSF11 = 1) then    
          (DATEDIFF(day, (convert(datetime,SUBSTRING(cast(INSDUE as varchar(10)),1,4)+'-'+SUBSTRING(cast(INSDUE as varchar(10)),5,2)+'-'+SUBSTRING(cast(INSDUE as varchar(10)),7,2))) , 
                   (convert(datetime,SUBSTRING(cast(INSILD as varchar(10)),1,4)+'-'+SUBSTRING(cast(INSILD as varchar(10)),5,2)+'-'+[dbo].[udf_GetEndOfMonth_DD](INSILD))) ))      
        --ยังไม่จ่าย
        when not(INSDUE is null) and not(INSDUE = 0) and ((INSILD is null) or (INSILD = 0)) then
          (DATEDIFF(day, (convert(datetime,SUBSTRING(cast(INSDUE as varchar(10)),1,4)+'-'+SUBSTRING(cast(INSDUE as varchar(10)),5,2)+'-'+SUBSTRING(cast(INSDUE as varchar(10)),7,2))) , 
                   (convert(datetime,SUBSTRING(@CurrentDate,1,4)+'-'+SUBSTRING(@CurrentDate,5,2)+'-'+SUBSTRING(@CurrentDate,7,2))) ))
        else 0    
        end ) as "DPD" , '  ' as DPD1 , '  ' as Grade , '  ' as CustGroup


into #TempINS01
from TMP_HPTINS00 
inner join TMP_INFOCOLLECTION on INSCRN = CONRUN
where INSCRN in
(
select b.INSCRN
from TMP_HPTINS00 b inner join TMP_INFOCOLLECTION c on b.INSCRN = c.CONRUN
where (b.INSCBR ='01') and (b.INSCPM ='2') and (b.INSCPT ='01')  
  and not(b.INSDUE is null ) and (b.INSDUE > 0) 
   and (b.INSILD >= @FirstPVDMonth) and (b.INSILD <= @LastPVDMonth) 
group by b.INSCRN
)
--(INSCBR ='01') and (INSCPM ='2') and (INSCPT ='01')  
  --and not(INSDUE is null ) and (INSDUE > 0) 
  --and (INSDUE >= convert(varchar(10),DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0),112)) 
  --and (INSDUE <= convert(varchar(10),DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)),112)) 

  --and INSCRN = '2054882' 
  --and (HPTINS07.INSDUE <= @ASDATEINT)              
  --(17663914 row(s) affected) 00:02:30 hr 

--alter table #TempINS01 add DPD1 varchar(1);
--alter table #TempINS01 add Grade varchar(2);

update #TempINS01 set DPD = 0 where (DPD < 0);  --(8487402 row(s) affected) 00:00:21
update #TempINS01 set DPD = 0 where (CONSTS in ('91','92')) and (INSILD = 0 ); --(971 row(s) affected) 00:00:00
update #TempINS01 set DPD = 0 where (CONSTS = 90 and DPD > 0);--(357 row(s) affected) 00:00:00
update #TempINS01 set DPD = null where (CONSTS < 49 and INSDUE > @LastPVDMonth and INSILD = 0);--(5880824 row(s) affected) 00:00:15

update #TempINS01 set DPD1 = '0' where (DPD >= 0 and DPD < 8); --(5860481 row(s) affected)
update #TempINS01 set DPD1 = '1' where (DPD > 7 and DPD < 31); --(6000895 row(s) affected)
update #TempINS01 set DPD1 = '2' where (DPD > 30 and DPD < 61); --(239511 row(s) affected)
update #TempINS01 set DPD1 = '3' where (DPD > 60 and DPD < 91); --(78092 row(s) affected)
update #TempINS01 set DPD1 = '4' where (DPD > 90 and DPD < 121); --(23331 row(s) affected)
update #TempINS01 set DPD1 = '5' where (DPD > 120 and DPD < 151); --(9659 row(s) affected)
update #TempINS01 set DPD1 = '6' where (DPD > 150 and DPD < 181); --(5196 row(s) affected)
update #TempINS01 set DPD1 = '99' where (DPD > 180 ); --(10726 row(s) affected)
--Total update 8 process useage time 00:04:48

/*
--------------
select top 10 a.*, b.conper from #TempINS01 a inner join  Tmp_infocollection b on b.CONRUN = a.INSCRN where a.INSCRN = '4000231' 
--------------
*/


--select top 10 * from #TempINS01  where  DPD is null and INSITM<12
--select * from #TempINS01  where  Grade = '  ' 
--select * from #TempINS02  where INSCRN = '3012970' order by INSITM
--select * from #TempINS01  where INSCRN = '0000069'
--select * from #TempData1 where INSCRN = '2053391'
--select * from #TempGA2
--update #TempINS01 set #TempINS01.Grade = '  ' 
--select INSCRN from #TempINS02 b where consts!=91 --b.INSITM >12 and b.DPD is null
--group by b.INSCRN having count (b.INSITM)<12
--select top 100 * from #TempINS02 b where b.INSITM >11 and b.DPD is null

--########## Compare Next DPD ###########
drop table #TempINS02 ;
select a.*,b.DPD1 as NextDPD1 
into #TempINS02
from #TempINS01 a 
left join #TempINS01 b on b.INSCRN = a.INSCRN and b.INSITM = (a.INSITM+1)
order by a.INSITM


--########## DATA <12 ###########
drop table #TempData1 ;
select a.INSCRN, sum(case when (a.DPD1='0' or a.DPD1='1' ) then 1 else 0 end) as sum1,
count(*) as sumTenor,sum(case when (a.DPD1='1') then 1 else 0 end) as sumDPD1 ,
 sum(case when (a.DPD1='1' and a.NextDPD1='1') then 1 else 0 end) as sumDupDPD1,
 sum(case when (a.DPD1!='0' and a.DPD1!='1' ) then 1 else 0 end) as sum2345699
into #TempData1
from (select * from #TempINS02 c where c.INSCRN in 
(select b.INSCRN from #TempINS01 b
group by b.INSCRN having max(b.INSITM)<12 union
select b.INSCRN from #TempINS01 b where b.INSITM =12 and b.DPD is null ) )  a 
where a.INSITM <12 and a.DPD is not null
group by a.INSCRN




--############ GB #############
drop table #TempGB ;
select * 
into #TempGB
from #TempData1 table1
where table1.sum1 = table1.sumTenor  
and (table1.sumDPD1 <=1)  
--########## UPDATE GB ##############
update #TempINS01 set #TempINS01.Grade = 'GB' 
FROM #TempINS01 t2
INNER JOIN #TempGB GB on GB.INSCRN = t2.INSCRN
where t2.INSCRN =GB.INSCRN and t2.Grade ='  '  

--############ GC #############
drop table #TempGC ;
select * 
into #TempGC
from #TempData1 table1
where table1.sum1 = table1.sumTenor  
and (table1.sumDupDPD1 =0)  
--########## UPDATE GC ##############
update #TempINS01 set #TempINS01.Grade = 'GC' 
FROM #TempINS01 t2
INNER JOIN #TempGC GC on GC.INSCRN = t2.INSCRN
where t2.INSCRN =GC.INSCRN and t2.Grade ='  '  

--############ GD #############
drop table #TempGD ;
select * 
into #TempGD
from #TempData1 table1
where table1.sum1 = table1.sumTenor  
and (table1.sumDPD1 >1)  
--########## UPDATE GD ##############
update #TempINS01 set #TempINS01.Grade = 'GD' 
FROM #TempINS01 t2
INNER JOIN #TempGD GD on GD.INSCRN = t2.INSCRN
where t2.INSCRN =GD.INSCRN and t2.Grade ='  '

--############ GE #############
drop table #TempGE ;
select * 
into #TempGE
from #TempData1 table1
where (table1.sum2345699 >=1)  
--########## UPDATE GE ##############
update #TempINS01 set #TempINS01.Grade = 'GE' 
FROM #TempINS01 t2
INNER JOIN #TempGE GE on GE.INSCRN = t2.INSCRN
where t2.INSCRN =GE.INSCRN and t2.Grade ='  '




--############################ DATA #######################################################

drop table #TempData2 ;
select a.INSCRN, sum(case when (a.DPD1='0' or a.DPD1='1' or a.DPD is null) then 1 else 0 end) as sum1,
count(*) as sumTenor,sum(case when (a.DPD1='1') then 1 else 0 end) as sumDPD1 ,
 sum(case when (a.INSITM<=12 and a.DPD1='1' ) then 1 else 0 end) as sumYear1,
 sum(case when (a.INSITM>12 and a.INSITM<=24 and a.DPD1='1' ) then 1 else 0 end) as sumYear2,
 sum(case when (a.INSITM>24 and a.INSITM<=36 and a.DPD1='1' ) then 1 else 0 end) as sumYear3,
 sum(case when (a.INSITM>36 and a.INSITM<=48 and a.DPD1='1' ) then 1 else 0 end) as sumYear4,
 sum(case when (a.INSITM>48 and a.INSITM<=60 and a.DPD1='1' ) then 1 else 0 end) as sumYear5,
 sum(case when (a.INSITM>60 and a.INSITM<=72 and a.DPD1='1' ) then 1 else 0 end) as sumYear6,
 sum(case when (a.INSITM>72 and a.INSITM<=84 and a.DPD1='1' ) then 1 else 0 end) as sumYear7,
 sum(case when (a.DPD1='1' or a.DPD1='2') then 1 else 0 end) as sum12,
 sum(case when (a.DPD1='0' or a.DPD1='1' or a.DPD1='2') then 1 else 0 end) as sum012

into #TempData2
from #TempINS01 a 
group by a.INSCRN

--############ GA #############
drop table #TempGA2 ;
select table1.INSCRN 
into #TempGA2
from #TempData2 table1
where table1.sum1 = table1.sumTenor 
and (table1.sumDPD1 <=1)  
and (table1.sumYear1<=1)
and (table1.sumYear2<=1)
and (table1.sumYear3<=1)
and (table1.sumYear4<=1)
and (table1.sumYear5<=1)
and (table1.sumYear6<=1)
and (table1.sumYear7<=1) 

--########## UPDATE GA ##############
update #TempINS01 set #TempINS01.Grade = 'GA' 
FROM #TempINS01 t2
INNER JOIN #TempGA2 GA on GA.INSCRN = t2.INSCRN
where t2.INSCRN =GA.INSCRN  

--############ GB Month >11#############
drop table #TempGB2 ;
select table1.INSCRN 
into #TempGB2
from #TempData2 table1
where table1.sum1 = table1.sumTenor 

--##########UPDATE GB Month >11##############
update #TempINS01 set #TempINS01.Grade = 'GB' 
FROM #TempINS01 t2
INNER JOIN #TempGB2 GB on GB.INSCRN = t2.INSCRN
where t2.INSCRN =GB.INSCRN and t2.Grade ='  '

--############ GC #############
drop table #TempGC2 ;
select table1.INSCRN 
into #TempGC2
from #TempData2 table1
where table1.sum012 = table1.sumTenor  
and table1.sum12 <= table1.sumTenor*0.05 

--########## UPDATE GC ##############
update #TempINS01 set #TempINS01.Grade = 'GC' 
FROM #TempINS01 t2
INNER JOIN #TempGC2 GC on GC.INSCRN = t2.INSCRN
where t2.INSCRN =GC.INSCRN and t2.Grade ='  '

--############ GD #############
drop table #TempGD2 ;
select table1.INSCRN 
into #TempGD2
from #TempData2 table1
where table1.sum012 = table1.sumTenor  
and table1.sum012 >=1 

--########## UPDATE GD ##############
update #TempINS01 set #TempINS01.Grade = 'GD' 
FROM #TempINS01 t2
INNER JOIN #TempGD2 GD on GD.INSCRN = t2.INSCRN
where t2.INSCRN =GD.INSCRN and t2.Grade ='  '

--############ GE #############

--########## UPDATE GE ##############
update #TempINS01 set Grade = 'GE' 
where Grade ='  '





--########## Customer Group ##############
 --2053391
--select * from #TempINS01  where INSCRN = '2003115' order by INSITM;



--########## Case 1 CustGroup 03 (DFD1>XXX Month)##############
drop table #TempCustGroup11;
select  a.INSCRN
into #TempCustGroup11
from #TempINS01 a where a.DPD1< (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
group by a.INSCRN having count(*) =@InputMonth ;

update #TempINS01 set #TempINS01.CustGroup = '03' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup11 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 1 CustGroup 01 (DPD1 XXX Month = 0 ,Last Month =1)##############
drop table #TempCustGroup12;
--declare @InputMonth INT  
--set @InputMonth =3;
select a.INSCRN
into #TempCustGroup12
from #TempINS01 a where a.DPD1 =0 
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) = 1
group by a.INSCRN having count(*) =@InputMonth ;
--select * from #TempCustGroup12;
update #TempINS01 set #TempINS01.CustGroup = '01' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup12 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 2 CustGroup 01 (DFD1=XXX Month,Last Month =0)##############
drop table #TempCustGroup211;
--declare @InputMonth INT  
--set @InputMonth =3;
select a.INSCRN
into #TempCustGroup211
from #TempINS01 a where a.DPD1= 0
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) =0
group by a.INSCRN having count(*) =@InputMonth;

update #TempINS01 set #TempINS01.CustGroup = '02' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup211 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 2 CustGroup 02 (DFD1=XXX Month,Last Month =1)##############
drop table #TempCustGroup212;
--declare @InputMonth INT  
--set @InputMonth =3;
select a.INSCRN
into #TempCustGroup212
from #TempINS01 a where a.DPD1= 1
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) =1
group by a.INSCRN having count(*) =@InputMonth;

update #TempINS01 set #TempINS01.CustGroup = '02' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup212 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 2 CustGroup 02 (DFD1=XXX Month,Last Month =2)##############
drop table #TempCustGroup213;
select a.INSCRN
into #TempCustGroup213
from #TempINS01 a where a.DPD1= 2
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) =2

group by a.INSCRN having count(*) =@InputMonth;

update #TempINS01 set #TempINS01.CustGroup = '02' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup213 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 2 CustGroup 03 (DFD1=XXX Month,Last Month >2)##############
drop table #TempCustGroup214;
select a.INSCRN
into #TempCustGroup214
from #TempINS01 a where a.DPD1 =(select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) >2
group by a.INSCRN having count(*) =@InputMonth;

update #TempINS01 set #TempINS01.CustGroup = '03' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup214 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  



--########## Case 3.1 CustGroup 01 (DFD1<=XXX Month,LastMonth DFD1 = 0) ##############
drop table #TempCustGroup311;
select a.INSCRN
into #TempCustGroup311
from #TempINS01 a where a.DPD1>= (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)=0
group by a.INSCRN having count(*) =@InputMonth;


update #TempINS01 set #TempINS01.CustGroup = '01' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup311 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 3.1 CustGroup 02 (DFD1<=XXX Month,LastMonth DFD1 = 1) ##############
drop table #TempCustGroup312;
select a.INSCRN
into #TempCustGroup312
from #TempINS01 a where a.DPD1>= (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) =1
group by a.INSCRN having count(*) =@InputMonth;


update #TempINS01 set #TempINS01.CustGroup = '02' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup312 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 3.1 CustGroup 02 (DFD1<=XXX Month,LastMonth DFD1 = 2) ##############
drop table #TempCustGroup313;
select a.INSCRN
into #TempCustGroup313
from #TempINS01 a where a.DPD1>= (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
--and a.INSCRN = '2053391'
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) =2
group by a.INSCRN having count(*) =@InputMonth;


update #TempINS01 set #TempINS01.CustGroup = '02' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup313 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  

--########## Case 3.2 CustGroup 03 (DFD1<=XXX Month,LastMonth DFD1 >2) ##############
drop table #TempCustGroup321;
select a.INSCRN
into #TempCustGroup321
from #TempINS01 a where (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) >2
group by a.INSCRN ;


update #TempINS01 set #TempINS01.CustGroup = '03' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup321 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  


--########## ex 1001,1101,0111,0011 have 0,1 ##############
drop table #TempCustGroup331;
select a.INSCRN
into #TempCustGroup331
from #TempINS01 a where a.DPD1<2
and a.INSITM >= ((select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)-@InputMonth) 
and a.INSITM < (select top 1 b.INSITM
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc)
and (select top 1 CONVERT(INT, b.DPD1)
from #TempINS01 b where b.INSCRN = a.INSCRN and b.DPD is not null order by b.INSITM desc) <2
group by a.INSCRN having count(*) =@InputMonth and min(a.DPD1) =0 ;

update #TempINS01 set #TempINS01.CustGroup = '01' 
FROM #TempINS01 t2
INNER JOIN #TempCustGroup331 CG on CG.INSCRN = t2.INSCRN
where t2.INSCRN =CG.INSCRN and t2.CustGroup ='  '  


--########## 0021 ##############

update #TempINS01 set CustGroup = '02' 
where CustGroup ='  '  ;



drop table #TempY;
drop table #Temp03;
select INSCRN,Grade,CustGroup,INSITM,DPD1 into #Temp03 from  #TempINS01;
select *  into #TempY from #Temp03
pivot (max (DPD1) for INSITM in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
                               [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
                               [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
                               [31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
                               [41],[42],[43],[44],[45],[46],[47],[48],[49],[50],
                               [51],[52],[53],[54],[55],[56],[57],[58],[59],[60],
                               [61],[62],[63],[64],[65],[66],[67],[68],[69],[70],
                               [71],[72],[73],[74],[75],[76],[77],[78],[79],[80],
                               [81],[82],[83],[84]   )) as PaymentHistory

insert into [Cus_History_Payment] (FUDBRN,FUDPDM,FUDPDT,FUDRUN,CUSGRADE,CUSGROUP,[INS01]
      ,[INS02]
      ,[INS03]
      ,[INS04]
      ,[INS05]
      ,[INS06]
      ,[INS07]
      ,[INS08]
      ,[INS09]
      ,[INS10]
      ,[INS11]
      ,[INS12]
      ,[INS13]
      ,[INS14]
      ,[INS15]
      ,[INS16]
      ,[INS17]
      ,[INS18]
      ,[INS19]
      ,[INS20]
      ,[INS21]
      ,[INS22]
      ,[INS23]
      ,[INS24]
      ,[INS25]
      ,[INS26]
      ,[INS27]
      ,[INS28]
      ,[INS29]
      ,[INS30]
      ,[INS31]
      ,[INS32]
      ,[INS33]
      ,[INS34]
      ,[INS35]
      ,[INS36]
      ,[INS37]
      ,[INS38]
      ,[INS39]
      ,[INS40]
      ,[INS41]
      ,[INS42]
      ,[INS43]
      ,[INS44]
      ,[INS45]
      ,[INS46]
      ,[INS47]
      ,[INS48]
      ,[INS49]
      ,[INS50]
      ,[INS51]
      ,[INS52]
      ,[INS53]
      ,[INS54]
      ,[INS55]
      ,[INS56]
      ,[INS57]
      ,[INS58]
      ,[INS59]
      ,[INS60]
      ,[INS61]
      ,[INS62]
      ,[INS63]
      ,[INS64]
      ,[INS65]
      ,[INS66]
      ,[INS67]
      ,[INS68]
      ,[INS69]
      ,[INS70]
      ,[INS71]
      ,[INS72]
      ,[INS73]
      ,[INS74]
      ,[INS75]
      ,[INS76]
      ,[INS77]
      ,[INS78]
      ,[INS79]
      ,[INS80]
      ,[INS81]
      ,[INS82]
      ,[INS83]
      ,[INS84],FUDSTS,FUDCRD,FUDCRU,FUDCRW )
select '01','2','01',*,'A',getdate(),'SYSTEM','SERVER' from #TempY y
WHERE
   NOT EXISTS (SELECT * FROM [Cus_History_Payment] c
              WHERE y.INSCRN = c.FUDRUN);


update [Cus_History_Payment] set 

	   [FUDBRN] = '01'
      ,[FUDPDM] = '2'
      ,[FUDPDT] = '01'
      ,[FUDRUN] = y.INSCRN
      ,[CUSGRADE] = y.Grade
      ,[CUSGROUP] = y.CustGroup
      ,[INS01] = y.[1]
      ,[INS02] = y.[2]
      ,[INS03] = y.[3]
      ,[INS04] = y.[4]
      ,[INS05] = y.[5]
      ,[INS06] = y.[6]
      ,[INS07] = y.[7]
      ,[INS08] = y.[8]
      ,[INS09] = y.[9]
      ,[INS10] = y.[10]
      ,[INS11] = y.[11]
      ,[INS12] = y.[12]
      ,[INS13] = y.[13]
      ,[INS14] = y.[14]
      ,[INS15] = y.[15]
      ,[INS16] = y.[16]
      ,[INS17] = y.[17]
      ,[INS18] = y.[18]
      ,[INS19] = y.[19]
      ,[INS20] = y.[20]
      ,[INS21] = y.[21]
      ,[INS22] = y.[22]
      ,[INS23] = y.[23]
      ,[INS24] = y.[24]
      ,[INS25] = y.[25]
      ,[INS26] = y.[26]
      ,[INS27] = y.[27]
      ,[INS28] = y.[28]
      ,[INS29] = y.[29]
      ,[INS30] = y.[30]
      ,[INS31] = y.[31]
      ,[INS32] = y.[32]
      ,[INS33] = y.[33]
      ,[INS34] = y.[34]
      ,[INS35] = y.[35]
      ,[INS36] = y.[36]
      ,[INS37] = y.[37]
      ,[INS38] = y.[38]
      ,[INS39] = y.[39]
      ,[INS40] = y.[40]
      ,[INS41] = y.[41]
      ,[INS42] = y.[42]
      ,[INS43] = y.[43]
      ,[INS44] = y.[44]
      ,[INS45] = y.[45]
      ,[INS46] = y.[46]
      ,[INS47] = y.[47]
      ,[INS48] = y.[48]
      ,[INS49] = y.[49]
      ,[INS50] = y.[50]
      ,[INS51] = y.[51]
      ,[INS52] = y.[52]
      ,[INS53] = y.[53]
      ,[INS54] = y.[54]
      ,[INS55] = y.[55]
      ,[INS56] = y.[56]
      ,[INS57] = y.[57]
      ,[INS58] = y.[58]
      ,[INS59] = y.[59]
      ,[INS60] = y.[60]
      ,[INS61] = y.[61]
      ,[INS62] = y.[62]
      ,[INS63] = y.[63]
      ,[INS64] = y.[64]
      ,[INS65] = y.[65]
      ,[INS66] = y.[66]
      ,[INS67] = y.[67]
      ,[INS68] = y.[68]
      ,[INS69] = y.[69]
      ,[INS70] = y.[70]
      ,[INS71] = y.[71]
      ,[INS72] = y.[72]
      ,[INS73] = y.[73]
      ,[INS74] = y.[74]
      ,[INS75] = y.[75]
      ,[INS76] = y.[76]
      ,[INS77] = y.[77]
      ,[INS78] = y.[78]
      ,[INS79] = y.[79]
      ,[INS80] = y.[80]
      ,[INS81] = y.[81]
      ,[INS82] = y.[82]
      ,[INS83] = y.[83]
      ,[INS84] = y.[84]  
      ,[FUDSTS] = 'A'
      ,[FUDCRD] = getdate()
      ,[FUDCRU] = 'SYSTEM'
      ,[FUDCRW] = 'SERVER'
FROM [Cus_History_Payment] t2
INNER JOIN #TempY y on y.INSCRN = t2.[FUDRUN]  
where t2.[FUDRUN] =y.INSCRN ;



end

END

--SP_HP_TestGradeCustomer

/* =============================   
drop table #TempX;

drop table #Temp02;


select INSCRN,Grade,INSITM,DPD into #Temp02 from  #TempINS01  where INSCRN = '4000231' --  drop table #Temp05

select * into #TempX from #Temp02
pivot (max (DPD) for INSITM in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
                               [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
                               [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
                               [31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
                               [41],[42],[43],[44],[45],[46],[47],[48],[49],[50],
                               [51],[52],[53],[54],[55],[56],[57],[58],[59],[60],
                               [61],[62],[63],[64],[65],[66],[67],[68],[69],[70],
                               [71],[72],[73],[74],[75],[76],[77],[78],[79],[80],
                               [81],[82],[83],[84]   )) as PaymentHistory


 where INSCRN = '4000231'
select top 10 * from #Temp03


select * from #TempX --order by INSCRN where INSCRN = '2054882' ; 
select * from #TempY --order by INSCRN where INSCRN = '2054882' ; 
select * from #TempINS01 where INSCRN = '4000231' ; 




*/