该存储过程用于统计贷款平台上的各种关键数据指标，包括放款笔数、放款金额、应收款笔数及金额等，并支持按日期范围进行查询。
USE [RC_RISK]
GO
/****** Object: StoredProcedure [dbo].[P_NewFinaceCount] Script Date: 2019/4/3 星期三 16:11:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--新财务统计
ALTER PROC [dbo].[P_NewFinaceCount]
(
@PageNo INT ,
@PageSize INT ,
@PlatformID INT ,
@StartTime VARCHAR(20) ,
@EndTime VARCHAR(20) ,
@Count INT OUTPUT
)
AS
BEGIN
WITH tree
AS (
--放款笔数
SELECT *
FROM ( SELECT 1 AS num ,
CONVERT(VARCHAR(10), PayTime, 23) AS dt ,
COUNT(1) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status >= 3
AND CONVERT(VARCHAR(10), PayTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), PayTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), PayTime, 23)
) AS LoanCount
--放款金额
UNION
SELECT *
FROM ( SELECT 2 AS num ,
CONVERT(VARCHAR(10), PayTime, 23) AS dt ,
SUM(LoanAmount) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status >= 3
AND CONVERT(VARCHAR(10), PayTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), PayTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), PayTime, 23)
) AS LoanAmount
--应收笔数
UNION
SELECT *
FROM ( SELECT 3 AS num ,
CONVERT(VARCHAR(10), CreateDate, 23) AS dt ,
[Count] AS count
FROM dbo.T_ReceivableCount
WHERE PlatformID = @PlatformID
AND CONVERT(VARCHAR(10), CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), CreateDate, 23) <= @EndTime
) AS ReceivableCount
--应收金额
UNION
SELECT *
FROM ( SELECT 4 AS num ,
CONVERT(VARCHAR(10), CreateDate, 23) AS dt ,
LoanAmount AS count
FROM dbo.T_ReceivableCount
WHERE PlatformID = @PlatformID
AND CONVERT(VARCHAR(10), CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), CreateDate, 23) <= @EndTime
) AS ReceivableAmount
--当天还款
UNION
SELECT *
FROM ( SELECT 5 AS num ,
CONVERT(VARCHAR(10), LastShouldReturnTime, 23) AS dt ,
COUNT(1) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status = 4
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) <= @EndTime
AND CONVERT(VARCHAR(10), RepaymentTime, 23) = CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
GROUP BY CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
) AS ReceivableRepaymentCount
--展期笔数
UNION
SELECT *
FROM ( SELECT 6 AS num ,
CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23) AS dt ,
COUNT(DISTINCT l.OrderNo) AS count
FROM dbo.T_Loans l
INNER JOIN dbo.T_Extension e ON l.ID = e.LoanID
WHERE PlatformID = @PlatformID
AND e.Status = 1
AND CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23)
) AS ExtensionCount
--未还笔数
UNION
SELECT *
FROM ( SELECT 7 AS num ,
CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23) AS dt ,
COUNT(1) AS count
FROM dbo.T_Loans l
WHERE PlatformID = @PlatformID
AND Status = 3
AND ID NOT IN (
SELECT LoanID
FROM dbo.T_Extension e
WHERE e.LoanID = ID
AND e.LastShouldReturnTime = l.LastShouldReturnTime
AND e.Status = 1 )
AND CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23)
) AS UnRepaymentCount
--今日提前还款
UNION
SELECT *
FROM ( SELECT 8 AS num ,
CONVERT(VARCHAR(10), RepaymentTime, 23) AS dt ,
COUNT(1) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status = 4
AND CONVERT(VARCHAR(10), RepaymentTime, 23) < CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
AND CONVERT(VARCHAR(10), RepaymentTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), RepaymentTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), RepaymentTime, 23)
) AS NowPrepaymentCount
--今日逾期还款
UNION
SELECT *
FROM ( SELECT 9 AS num ,
CONVERT(VARCHAR(10), RepaymentTime, 23) AS dt ,
COUNT(1) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status = 4
AND CONVERT(VARCHAR(10), RepaymentTime, 23) > CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
AND CONVERT(VARCHAR(10), RepaymentTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), RepaymentTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), RepaymentTime, 23)
) AS NowOverdueRepaymentCount
--今日提前展期笔数
UNION
SELECT *
FROM ( SELECT 10 AS num ,
CONVERT(VARCHAR(10), e.CreateDate, 23) AS dt ,
COUNT(DISTINCT l.OrderNo) AS count
FROM dbo.T_Loans l
INNER JOIN dbo.T_Extension e ON l.ID = e.LoanID
WHERE PlatformID = @PlatformID
AND e.Status = 1
AND CONVERT(VARCHAR(10), e.CreateDate, 23) < CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23)
AND CONVERT(VARCHAR(10), e.CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), e.CreateDate, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), e.CreateDate, 23)
) AS UpextensionCount
--今日逾期续期笔数
UNION
SELECT *
FROM ( SELECT 11 AS num ,
CONVERT(VARCHAR(10), e.CreateDate, 23) AS dt ,
COUNT(DISTINCT l.OrderNo) AS count
FROM dbo.T_Loans l
INNER JOIN dbo.T_Extension e ON l.ID = e.LoanID
WHERE PlatformID = @PlatformID
AND e.Status = 1
AND CONVERT(VARCHAR(10), e.CreateDate, 23) > CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23)
AND CONVERT(VARCHAR(10), e.CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), e.CreateDate, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), e.CreateDate, 23)
) AS OverextensionCount
--今日还款金额
UNION
SELECT *
FROM ( SELECT 12 AS num ,
CONVERT(VARCHAR(10), RepaymentTime, 23) AS dt ,
SUM(AlreadyRepaidAmount) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status = 4
AND CONVERT(VARCHAR(10), RepaymentTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), RepaymentTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), RepaymentTime, 23)
) AS RepaymentAmount
--今日展期费用
UNION
SELECT *
FROM ( SELECT 13 AS num ,
CONVERT(VARCHAR(10), e.CreateDate, 23) AS dt ,
SUM(e.ActualRepaymentAmount) AS count
FROM dbo.T_Loans l
INNER JOIN dbo.T_Extension e ON l.ID = e.LoanID
INNER JOIN dbo.T_LoanOrderDetails o ON o.LoanID = e.LoanID
WHERE l.PlatformID = @PlatformID
AND e.Status = 1
AND o.Status = 1
AND e.IsOwn = 0
AND o.PayType = 3
AND CONVERT(VARCHAR(10), e.CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), e.CreateDate, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), e.CreateDate, 23)
) AS ExtensionAmount
--未还金额
UNION
SELECT *
FROM ( SELECT 14 AS num ,
CONVERT(VARCHAR(10), LastShouldReturnTime, 23) AS dt ,
SUM(LoanAmount) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status = 3
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
) AS UnRepaymentAmount
--续借单数
UNION
SELECT *
FROM ( SELECT 15 AS num ,
CONVERT(VARCHAR(10), l.PayTime, 23) AS dt ,
COUNT(1) AS count
FROM T_Loans AS l
WHERE l.PlatformID = @PlatformID
AND l.[Status] IN ( 3, 4, 5 )
AND l.LoanCount > 1
AND CONVERT(VARCHAR(10), l.PayTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), l.PayTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), l.PayTime, 23)
) AS doubleLansSum
--续借/展期逾期数量
UNION
SELECT *
FROM ( SELECT 16 AS num ,
CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23) AS dt ,
COUNT(1) AS count
FROM T_Loans AS l
WHERE l.PlatformID = @PlatformID
AND l.[Status] = 3
AND l.LastShouldReturnTime < GETDATE()
AND ( l.LoanCount > 1
OR l.IsExtension = 1
)
AND CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23) < CONVERT(VARCHAR(10), GETDATE(), 23)
AND CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), l.LastShouldReturnTime, 23)
) AS doubleOverLans
--提前还款
UNION
SELECT *
FROM ( SELECT 17 AS num ,
CONVERT(VARCHAR(10), LastShouldReturnTime, 23) AS dt ,
COUNT(1) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status = 4
AND CONVERT(VARCHAR(10), RepaymentTime, 23) < CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
) AS PrepaymentCount
--逾期还款
UNION
SELECT *
FROM ( SELECT 18 AS num ,
CONVERT(VARCHAR(10), LastShouldReturnTime, 23) AS dt ,
COUNT(1) AS count
FROM dbo.T_Loans
WHERE PlatformID = @PlatformID
AND Status = 4
AND CONVERT(VARCHAR(10), RepaymentTime, 23) > CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), LastShouldReturnTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), LastShouldReturnTime, 23)
) AS OverdueRepaymentCount
--应收续借单数
UNION
SELECT *
FROM ( SELECT 19 AS num ,
CONVERT(VARCHAR(10), CreateDate, 23) AS dt ,
DoubleLoansIDCount AS count
FROM dbo.T_ReceivableCount
WHERE PlatformID = @PlatformID
AND CONVERT(VARCHAR(10), CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), CreateDate, 23) <= @EndTime
) AS shouldDoubleLansSum
--应收提前展期笔数的蠢货
UNION
SELECT *
FROM ( SELECT 20 AS num ,
CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23) AS dt ,
COUNT(DISTINCT l.OrderNo) AS count
FROM dbo.T_Loans l
INNER JOIN dbo.T_Extension e ON l.ID = e.LoanID
WHERE PlatformID = @PlatformID
AND e.Status = 1
AND e.LoanID NOT IN (
SELECT item
FROM T_ReceivableCount a
CROSS APPLY Fun_StringToIntArray(a.LoansID)
WHERE CONVERT(VARCHAR(10), a.CreateDate, 23) = CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23)
AND a.PlatformID = @PlatformID )
AND CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23) >= @StartTime
AND CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23) <= @EndTime
GROUP BY CONVERT(VARCHAR(10), e.LastShouldReturnTime, 23)
) AS shouldUpExtensionCount
--复贷每日还款笔数
UNION
SELECT *
FROM ( SELECT 21 AS num ,
temp.CreateDate AS dt ,
COUNT(1) AS count
FROM ( SELECT ids.ID AS LoanID ,
CONVERT(VARCHAR(10), ids.CreateDate, 23) AS CreateDate
FROM ( SELECT
item AS ID ,
r.CreateDate
FROM
T_ReceivableCount r
CROSS APPLY Fun_StringToIntArray(r.DoubleLoansID)
WHERE
CONVERT(VARCHAR(10), r.CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), r.CreateDate, 23) <= @EndTime
AND r.PlatformID = @PlatformID
) ids
INNER JOIN T_Loans AS l ON ids.ID = l.ID
WHERE l.Status = 4
UNION
SELECT ids.ID AS LoanID ,
CONVERT(VARCHAR(10), ids.CreateDate, 23) AS CreateDate
FROM ( SELECT
item AS ID ,
r.CreateDate
FROM
T_ReceivableCount r
CROSS APPLY Fun_StringToIntArray(r.DoubleLoansID)
WHERE
CONVERT(VARCHAR(10), r.CreateDate, 23) >= @StartTime
AND CONVERT(VARCHAR(10), r.CreateDate, 23) <= @EndTime
AND r.PlatformID = @PlatformID
) ids
INNER JOIN dbo.T_Extension
AS e ON ids.ID = e.LoanID
WHERE e.Status = 1
AND CONVERT(VARCHAR(10), CONVERT(DATETIME, e.LastShouldReturnTime), 23) = CONVERT(VARCHAR(10), ids.CreateDate, 23)
) temp
GROUP BY temp.CreateDate
) AS doublePayLans
)
SELECT *
INTO #TempTable
FROM ( SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY dt DESC ) AS rownum ,
dt ,
ISNULL([1], 0) AS LoanCount , --放款笔数
ISNULL([2], 0) AS LoanAmount , --放款金额
ISNULL([3], 0) AS ReceivableCount , --应收笔数
ISNULL([4], 0) AS ReceivableAmount , --应收金额
ISNULL([5], 0) AS ReceivableRepaymentCount , --当日还款
ISNULL([6], 0) AS ExtensionCount , --展期笔数
ISNULL([7], 0) AS UnRepaymentCount , --未还笔数
ISNULL([8], 0) AS NowPrepaymentCount , --今日提前还款数量
ISNULL([9], 0) AS NowOverdueRepaymentCount , --今日逾期还款数量
ISNULL([10], 0) AS UpextensionCount , --今日提前续期数量
ISNULL([11], 0) AS OverextensionCount , --今日逾期续期数量
ISNULL([12], 0) AS RepaymentAmount , --今日还款金额
ISNULL([13], 0) AS ExtensionAmount , --今日展期金额
ISNULL([14], 0) AS UnRepaymentAmount , --未还金额
ISNULL([15], 0) AS doubleLansSum , --续借单数
ISNULL([16], 0) AS doubleOverLans , --续借/展期逾期数量
ISNULL([17], 0) AS PrepaymentCount , --提前还款
ISNULL([18], 0) AS OverdueRepaymentCount , --逾期还款
ISNULL([19], 0) AS shouldDoubleLansSum , --应收续借单数
ISNULL([20], 0) AS shouldUpExtensionCount , --应收提前展期的蠢货
ISNULL([21], 0) AS doublePayLans --复贷每日还款笔数
FROM tree PIVOT ( SUM(count) FOR num IN ( [1],
[2], [3], [4],
[5], [6], [7],
[8], [9], [10],
[11], [12], [13],
[14], [15], [16],
[17], [18], [19],
[20], [21] ) ) AS p
) temp
WHERE temp.dt <> ''
) tp
ORDER BY dt DESC
OPTION ( RECOMPILE );
SELECT *
FROM #TempTable
WHERE rownum > ( @PageNo - 1 ) * @PageSize
AND rownum <= ( @PageNo * @PageSize );
SELECT @Count = COUNT(1)
FROM #TempTable;
--删除临时表
DROP TABLE #TempTable;
END;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
