34,594
社区成员
发帖
与我相关
我的任务
分享
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY 单号 ORDER BY 收款日期)RN,*FROM TB
)
,CTE2 AS(
SELECT
T1.RN+1 RN,T1.单号,T1.单据日期,T1.收款日期,T1.原单金额,T1.未付金额,T1.本次付款,T1.利息率万
,T1.未付金额-T1.本次付款 LASTMONEY
,T1.未付金额-T1.本次付款 [结算金额]
,ISNULL(T2.收款日期,GETDATE()) NEXTDATE
,DATEDIFF(DD,T1.收款日期,ISNULL(T2.收款日期,GETDATE())) [结算天数]
,CASE WHEN T2.RN IS NULL THEN 1 ELSE 0 END ISLAST
FROM
CTE T1
LEFT JOIN CTE T2 ON T1.RN+1=T2.RN AND T1.单号=T2.单号
UNION ALL
SELECT
1,单号,单据日期,收款日期,原单金额,未付金额,0,利息率万
,原单金额 LASTMONEY
,原单金额 [结算金额]
,收款日期 NEXTDATE
,DATEDIFF(DD,单据日期,收款日期) [结算天数]
,0
FROM
CTE WHERE RN=1
)
SELECT
*
,CAST([结算金额]*([结算天数])*利息率万/10000 AS DECIMAL(19,4))[无利滚利]
,CAST([结算金额]*(POWER(1+CAST(利息率万 AS REAL)/10000,[结算天数])-1) AS DECIMAL(19,4))[利滚利]
,SUM(CAST([结算金额]*([结算天数])*利息率万/10000 AS DECIMAL(19,4)))OVER(PARTITION BY 单号,ISLAST)[无利滚利至今利息]
,SUM(CAST([结算金额]*(POWER(1+CAST(利息率万 AS REAL)/10000,[结算天数])-1) AS DECIMAL(19,4)))OVER(PARTITION BY 单号,ISLAST)[利滚利至今利息]
FROM
CTE2
ORDER BY 单号,NEXTDATE
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY 单号 ORDER BY 收款日期)RN,*FROM TB
)
,CTE2 AS(
SELECT
T1.*
,T1.未付金额-T1.本次付款 LASTMONEY
,T1.未付金额-T1.本次付款 [结算金额]
,ISNULL(T2.收款日期,GETDATE()) NEXTDATE
,DATEDIFF(DD,T1.收款日期,ISNULL(T2.收款日期,GETDATE())) [结算天数]
,CASE WHEN T2.RN IS NULL THEN 1 ELSE 0 END ISLAST
FROM
CTE T1
LEFT JOIN CTE T2 ON T1.RN+1=T2.RN AND T1.单号=T2.单号
UNION ALL
SELECT
T1.*
,T1.原单金额 LASTMONEY
,T1.原单金额 [结算金额]
,T1.收款日期 NEXTDATE
,DATEDIFF(DD,T1.单据日期,T1.收款日期) [结算天数]
,0
FROM
CTE T1 WHERE T1.RN=1
)
SELECT
*
,CAST([结算金额]*([结算天数])*利息率万/10000 AS DECIMAL(19,4))[无利滚利]
,CAST([结算金额]*(POWER(1+CAST(利息率万 AS REAL)/10000,[结算天数])-1) AS DECIMAL(19,4))[利滚利]
,SUM(CAST([结算金额]*([结算天数])*利息率万/10000 AS DECIMAL(19,4)))OVER(PARTITION BY 单号,ISLAST)[无利滚利至今利息]
,SUM(CAST([结算金额]*(POWER(1+CAST(利息率万 AS REAL)/10000,[结算天数])-1) AS DECIMAL(19,4)))OVER(PARTITION BY 单号,ISLAST)[利滚利至今利息]
FROM
CTE2
ORDER BY 单号,NEXTDATE
CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY 单号 ORDER BY 收款日期)RN,*FROM TB
)
,CTE2 AS(
SELECT
T1.*
,T1.未付金额-T1.本次付款 LASTMONEY
,T1.未付金额-T1.本次付款 [结算金额]
,ISNULL(T2.收款日期,GETDATE()) NEXTDATE
,DATEDIFF(DD,T1.收款日期,ISNULL(T2.收款日期,GETDATE())) [结算天数]
,CASE WHEN T2.RN IS NULL THEN 1 ELSE 0 END ISLAST
FROM
CTE T1
LEFT JOIN CTE T2 ON T1.RN+1=T2.RN AND T1.单号=T2.单号
UNION ALL
SELECT
T1.*
,T1.原单金额 LASTMONEY
,T1.原单金额 [结算金额]
,T1.收款日期 NEXTDATE
,DATEDIFF(DD,T1.单据日期,T1.收款日期) [结算天数]
,0
FROM
CTE T1 WHERE T1.RN=1
)
SELECT
*
,CAST([结算金额]*([结算天数])*利息率万/10000 AS DECIMAL(19,4))[无利滚利]
,CAST([结算金额]*(POWER(1+CAST(利息率万 AS REAL)/10000,[结算天数])-1) AS DECIMAL(19,4))[利滚利]
,SUM(CAST([结算金额]*([结算天数])*利息率万/10000 AS DECIMAL(19,4)))OVER(PARTITION BY 单号,ISLAST)[无利滚利至今利息]
,SUM(CAST([结算金额]*(POWER(1+CAST(利息率万 AS REAL)/10000,[结算天数])-1) AS DECIMAL(19,4)))OVER(PARTITION BY 单号,ISLAST)[利滚利至今利息]
FROM
CTE2
ORDER BY 单号,NEXTDATE
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY 单号 ORDER BY 收款日期)RN,*FROM TB
)
,CTE2 AS(
SELECT
T1.*
,T1.未付金额-T1.本次付款 LASTMONEY
,CASE WHEN T1.RN=1 THEN T1.原单金额 ELSE T1.未付金额-T1.本次付款 END [结算金额]
,ISNULL(T2.收款日期,GETDATE()) NEXTDATE
,CASE WHEN T1.RN=1 THEN DATEDIFF(DD,T1.单据日期,T1.收款日期)ELSE DATEDIFF(DD,T1.收款日期,ISNULL(T2.收款日期,GETDATE())) END [结算天数]
,CASE WHEN T2.RN IS NULL THEN 1 ELSE 0 END ISLAST
FROM
CTE T1
LEFT JOIN CTE T2 ON T1.RN+1=T2.RN AND T1.单号=T2.单号
)
SELECT
T1.*
,CAST([结算金额]*(T1.[结算天数])*T1.利息率万/10000 AS DECIMAL(19,4))[无利滚利]
,CAST([结算金额]*(POWER(1+CAST(T1.利息率万 AS REAL)/10000,T1.[结算天数])-1) AS DECIMAL(19,4))[利滚利]
,SUM(CAST([结算金额]*(T1.[结算天数])*T1.利息率万/10000 AS DECIMAL(19,4)))OVER(PARTITION BY T1.单号,ISLAST)[无利滚利至今利息]
,SUM(CAST([结算金额]*(POWER(1+CAST(T1.利息率万 AS REAL)/10000,T1.[结算天数])-1) AS DECIMAL(19,4)))OVER(PARTITION BY T1.单号,ISLAST)[利滚利至今利息]
FROM
CTE2 T1
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY 单号 ORDER BY 收款日期)RN,*FROM TB
)
,CTE2 AS(
SELECT
T1.*
,T1.未付金额-T1.本次付款 LASTMONEY
,CASE WHEN T1.RN=1 THEN T1.原单金额 ELSE T1.未付金额-T1.本次付款 END [结算金额]
,ISNULL(T2.收款日期,GETDATE()) NEXTDATE
,CASE WHEN T1.RN=1 THEN DATEDIFF(DD,T1.单据日期,T1.收款日期)ELSE DATEDIFF(DD,T1.收款日期,ISNULL(T2.收款日期,GETDATE())) END [结算天数]
FROM
CTE T1
LEFT JOIN CTE T2 ON T1.RN+1=T2.RN AND T1.单号=T2.单号
)
SELECT
T1.*
,CAST([结算金额]*(T1.[结算天数])*T1.利息率万/10000 AS DECIMAL(19,4))[无利滚利]
,CAST([结算金额]*(POWER(1+CAST(T1.利息率万 AS REAL)/10000,T1.[结算天数])-1) AS DECIMAL(19,4))[利滚利]
,SUM(CAST([结算金额]*(T1.[结算天数])*T1.利息率万/10000 AS DECIMAL(19,4)))OVER(PARTITION BY T1.单号)[无利滚利至今利息]
,SUM(CAST([结算金额]*(T1.[结算天数])*T1.利息率万/10000 AS DECIMAL(19,4)))OVER(PARTITION BY T1.单号)[利滚利至今利息]
FROM
CTE2 T1
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY 单号 ORDER BY 收款日期)RN,*FROM TB
)
,CTE2 AS(
SELECT
T1.*
,T1.未付金额-T1.本次付款 LASTMONEY
,ISNULL(T2.收款日期,GETDATE()) NEXTDATE
,CASE WHEN T1.RN=1 THEN DATEDIFF(DD,T1.单据日期,T1.收款日期)ELSE DATEDIFF(DD,T1.收款日期,ISNULL(T2.收款日期,GETDATE())) END [结算天数]
FROM
CTE T1
LEFT JOIN CTE T2 ON T1.RN+1=T2.RN AND T1.单号=T2.单号
)
SELECT
T1.*
,CAST((T1.未付金额-T1.本次付款)*(T1.[结算天数])*T1.利息率万/10000 AS DECIMAL(19,4))[无利滚利]
,CAST((T1.未付金额-T1.本次付款)*(POWER(1+CAST(T1.利息率万 AS REAL)/10000,T1.[结算天数])-1) AS DECIMAL(19,4))[利滚利]
FROM
CTE2 T1
你看下,是不是这样