Project

Profile

Help

HostedRedmine.com has moved to the Planio platform. All logins and passwords remained the same. All users will be able to login and use Redmine just as before. Read more...
Download (53.1 KB)

MS Store Procedure การจัดเกรดลูกค้า » monthly.sql

Jakapan Kanta, 2015-11-04 06:41 PM

 
??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; --@-2D'I*-
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
--H2"!2#4
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))) ))
--H2"!22*H'
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))) ))
--"1D!HH2"
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.INSIT