Shulou(Shulou.com)06/01 Report--

IF EXISTS (SELECT FROM SYS.TABLES WHERE NAME='SPD') DROP TABLE SPD

WITH CTE1 AS

(

SELECT

IIF (PD_DueDT > = ISNULL (PD_DebitSuccDT,'9999-12-31'), 0recover1) AS FLAG/ overdue FLAG/

ROW_NUMBER () OVER (PARTITION BY PD_EFH_SN ORDER BY PD_Termth) AS ID/ session serial number /

MAX (PD_Termth) OVER (PARTITION BY PD_EFH_SN) AS CurrID/ current maximum session serial number * /

FROM PD WHERE PD_DueDT0

),

CTE2 AS

(

SELECT

SUM (FLAG) OVER (PARTITION BY PD_EFH_SN ORDER BY PD_Termth ROWS UNBOUNDED PRECEDING) AS AccuFlag/ accumulated overdue periods /

FROM CTE1

),

CTE3 AS

(

SELECT

ID-AccuFlag AS FlagGrp/ overdue Group /

FROM CTE2

),

CTE4 AS

(

SELECT

SUM (FLAG) OVER (PARTITION BY PD_EFH_SN,FlagGrp) AS CountFlag/ packet count /

FROM CTE3 WHERE FLAG=1

),

CTE5 AS

(

SELECT

MAX (CountFlag) OVER (PARTITION BY PD_EFH_SN) AS MaxContFlag/ maximum continuous /

MAX (AccuFlag) OVER (PARTITION BY PD_EFH_SN) AS TotFlag/ Historical accumulation /

IIF (ID=PD_TotTerm,CountFlag,0) AS CurrContFlag/ current cumulative /

ROW_NUMBER () OVER (PARTITION BY PD_EFH_SN ORDER BY PD_Termth DESC) AS CurrTermth

FROM CTE4

)

SELECT DISTINCT PD_EFH_SN,MaxContFlag,TotFlag,CurrContFlag,'XFY' AS DataSource INTO SPD FROM CTE5 WHERE CurrTermth=1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.