sql - 使用 Microsoft sql server 中的分区方案查找最近 6 个月的付款

Question

这是这篇文章的后续内容。我现在要做的是总结过去 6 个月的总付款。例如，我们有这笔贷款

正如您所看到的，他们在 4 月份支付了 3 笔款项，我需要做的是将这些款项相加以获得净额。目前我的查询只找到其中​​一个并采用那个，但这是不正确的。我试图做的是：

payments as
(
SELECT ROW_NUMBER() OVER(Partition By Account ORDER BY CONVERT(datetime,DateRec)  DESC) AS [RowNumber], Total as [Total], Account, SourceTyp
FROM mars.dbo.vw_PaymentHistory
WHERE SourceTyp = 'RegPmt'
GROUP BY Total, Account, DateRec, SourceTyp
)
,

get_payment1 as
(
SELECT * FROM payments
where RowNumber = 1 AND SourceTyp = 'RegPmt'
)
,

get_payment2 as
(
SELECT * FROM payments
where RowNumber = 2 AND SourceTyp = 'RegPmt'
),

get_payment3 as
(
SELECT * FROM payments
where RowNumber = 3 AND SourceTyp = 'RegPmt'
),

get_payment4 as
(
SELECT * FROM payments
where RowNumber = 4 AND SourceTyp = 'RegPmt'
),

get_payment5 as
(
SELECT * FROM payments
where RowNumber = 5 AND SourceTyp = 'RegPmt'
),

get_payment6 as
(
SELECT * FROM payments
where RowNumber = 6 AND SourceTyp = 'RegPmt'
)

但是对于上面的贷款，我只得到负值，我需要的是指定月份的所有付款的总和DateRec。我不确定此时该怎么做才能达到结果。

这是我到目前为止的整个查询：

Declare @snapshotdate date = '5/12/2020',
        @monthStart date = '5/1/2020';



WITH Active_Loans as (                         
SELECT 
    la.Account, 
    la.LoanStatus, 
    la.PrinBal, 
    isnull(b.Amount, 0) [DUPB],
    la.PrinBal + isnull(b.Amount, 0) [TotalUPB],
    l.NoteOwner,
    pt.[Partition],
    l.paidoffdate,
    la.[First Name],
    la.[Last Name],
    la.PmtPI,
    la.PmtImpound,
    la.NextDueDate,
    la.MaturityDate,
    la.NoteOwner as [Note Owner]
FROM MARS_DW..vw_Loans_Archive la
LEFT JOIN MARS_DW..vw_DUPBByLoan b on b.Account = la.Account
    AND b.ArchiveDate = la.ArchiveDate
LEFT JOIN MARS..vw_Loans l on l.Account = la.Account
LEFT JOIN Portfolio_Analytics..partition_table pt on pt.Noteowner = l.NoteOwner
WHERE la.ArchiveDate = @snapshotdate
    AND la.isActive = 1 
    --AND la.PaidOffDate is null 
    --AND la.LoanStatus NOT LIKE 'BK Payment Plan' 
    --AND la.LoanStatus NOT LIKE 'Prelim' 
    --AND la.LoanStatus NOT like 'trailing claims'
    --AND la.Account NOT IN (
    --                  SELECT account
    --                  FROM MARS..vw_Loans
    --                  WHERE servicexferdate <= 
    --                  DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, 0, @monthStart) + 1, 0))
    --                      AND PaidOffDate BETWEEN @monthStart AND DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, 0, @monthStart) + 1, 0))
    --                     )
UNION
(
SELECT l.account
    ,la.LoanStatus
    ,la.PrinBal
    ,isnull(b.Amount, 0) [DUPB]
    ,la.PrinBal + isnull(b.Amount, 0) [TotalUPB]
    ,l.NoteOwner 
    ,pt.[Partition]
    ,l.PaidOffDate
    ,la.[First Name]
    ,la.[Last Name]
    ,la.PmtPI
    ,la.PmtImpound
    ,la.NextDueDate
    ,la.MaturityDate
    ,la.NoteOwner as [Note Owner]
FROM MARS..vw_Loans l
LEFT JOIN MARS_DW..vw_Loans_Archive la on la.Account = l.Account
LEFT JOIN MARS_DW..vw_DUPBByLoan b on b.Account = la.Account
LEFT JOIN Portfolio_Analytics..partition_table pt on pt.Noteowner = l.NoteOwner
AND b.ArchiveDate = la.ArchiveDate
WHERE l.servicexferdate < @snapshotdate
    AND l.PaidOffDate > @snapshotdate
    AND la.ArchiveDate = @snapshotdate
    AND la.LoanStatus NOT like 'trailing claims'
    AND la.LoanStatus NOT like 'Inactive - REO/FC'
    AND pt.[Partition] IS NOT NULL
)
)
,

payments as
(
SELECT ROW_NUMBER() OVER(Partition By Account ORDER BY CONVERT(datetime,DateRec)  DESC) AS [RowNumber], Total as [Total], Account, SourceTyp
FROM mars.dbo.vw_PaymentHistory
WHERE SourceTyp = 'RegPmt'
GROUP BY Total, Account, DateRec, SourceTyp
)
,

get_payment1 as
(
SELECT * FROM payments
where RowNumber = 1 AND SourceTyp = 'RegPmt'
)
,

get_payment2 as
(
SELECT * FROM payments
where RowNumber = 2 AND SourceTyp = 'RegPmt'
),

get_payment3 as
(
SELECT * FROM payments
where RowNumber = 3 AND SourceTyp = 'RegPmt'
),

get_payment4 as
(
SELECT * FROM payments
where RowNumber = 4 AND SourceTyp = 'RegPmt'
),

get_payment5 as
(
SELECT * FROM payments
where RowNumber = 5 AND SourceTyp = 'RegPmt'
),

get_payment6 as
(
SELECT * FROM payments
where RowNumber = 6 AND SourceTyp = 'RegPmt'
)





SELECT 

rptpop.Account
, rptpop.LoanStatus
, rptpop.[First Name]
, rptpop.[Last Name]
, '$' + CONVERT (VARCHAR (12), rptpop.PmtPI+rptpop.PmtImpound, 1) as PITI
,'$' + CONVERT (VARCHAR (12), rptpop.TotalUPB, 1) as [Total UPB]
, CONVERT(VARCHAR(10),rptpop.NextDueDate,101) as [Next Due Date]
, CONVERT(VARCHAR(10),rptpop.MaturityDate,101) as [Maturity Date]
, rptpop.[Note Owner]
, '$' + CONVERT (VARCHAR (12),c1.Total , 1) as [c1]
, '$' + CONVERT (VARCHAR (12),c2.Total , 1) as [c2]
, '$' + CONVERT (VARCHAR (12),c3.Total , 1) as [c3]
, '$' + CONVERT (VARCHAR (12),c4.Total , 1) as [c4]
, '$' + CONVERT (VARCHAR (12),c5.Total , 1) as [c5]
, '$' + CONVERT (VARCHAR (12),c6.Total , 1) as [c6]


FROM Active_Loans as rptpop
LEFT JOIN get_payment1 as c1 on c1.Account = rptpop.Account
LEFT JOIN get_payment2 as c2 on c2.Account = rptpop.Account
LEFT JOIN get_payment3 as c3 on c3.Account = rptpop.Account
LEFT JOIN get_payment4 as c4 on c4.Account = rptpop.Account
LEFT JOIN get_payment5 as c5 on c5.Account = rptpop.Account
LEFT JOIN get_payment6 as c6 on c6.Account = rptpop.Account

WHERE 
rptpop.Partition  = 'GAEA'
AND rptpop.[Last Name] NOT LIKE '%CRE%'

如果我的要求不清楚，请告诉我。同样，我只是想创建一种方法来查找每笔贷款在 6 个月内支付的所有款项，我认为它只需要在我之前发布的帖子中进行轻微修改。

编辑：

我也尝试过这样的事情：

payments as
(
SELECT ROW_NUMBER() OVER(Partition By Account ORDER BY CONVERT(datetime,DateRec)  DESC) AS [RowNumber], Account
, SUM(Total) OVER(Partition By Account ORDER BY CONVERT(datetime,DateRec)  DESC) AS Total
FROM mars.dbo.vw_PaymentHistory
WHERE SourceTyp = 'RegPmt'
)
,

但我得到这个错误

Msg 102, Level 15, State 1, Line 76
Incorrect syntax near 'order'.
Msg 102, Level 15, State 1, Line 87
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 93
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 99
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 105
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 111
Incorrect syntax near ','.

编辑

为了明确这一点，这是我应该得到的输出：

标签： sqlsql-serverpivotpartitioning

Answer 1

您应该从总结每个月的付款开始，并根据要求为他们分配 row_numbers 或过滤过去 6 个月。您可以使用下面的伪代码获得最后 6 笔付款。

WITH payments as
(
SELECT 
ROW_NUMBER() OVER(Partition By Account ORDER BY CONVERT(VARCHAR(6),DateRec,112) DESC) as rn,
'C' + Convert(char(4),ROW_NUMBER() OVER(Partition By Account ORDER BY CONVERT(VARCHAR(6),DateRec,112) DESC)) AS [pmnt_Number], 
Account, 
CONVERT(VARCHAR(6),DateRec,112) as pmnt_month,
SUM(Total) AS Total_payment
FROM tbl_PaymentHistory
WHERE SourceTyp = 'RegPmt'
GROUP BY Account, CONVERT(VARCHAR(6),DateRec,112)
)

Select * from payments WHERE rn <= 6

一旦您的帐户的最后 6 个月付款具有其他属性，那么您可以进行数据透视以获得您提到的输出，或者您也可以使用左连接方法，但您可以简单地使用付款 CTE 而不是创建 6 个 CTE。

带枢轴选项

Select account, ISNULL([C1],0) as [C1],ISNULL([C2],0) as [C2],ISNULL([C3],0) as [C3],ISNULL([C4],0) as [C4],ISNULL([C5],0) as [C5],ISNULL([C6],0) as [C6]
from
(
SELECT account,[pmnt_number],[total_payments]
FROM payments ) AS source_tbl
PIVOT
(
MIN([Total_payments]) 
FOR [pmnt_number] IN ([C1],[C2],[C3],[C4],[C5],[C6])
) AS PVTTable

左连接

SELECT 
rptpop.Account,
rptpop.xyz
, '$' + CONVERT (VARCHAR (12),c1.[Total_payments] , 1) as [c1]
, '$' + CONVERT (VARCHAR (12),c2.[Total_payments] , 1) as [c2]
, '$' + CONVERT (VARCHAR (12),c3.[Total_payments] , 1) as [c3]
, '$' + CONVERT (VARCHAR (12),c4.[Total_payments] , 1) as [c4]
, '$' + CONVERT (VARCHAR (12),c5.[Total_payments] , 1) as [c5]
, '$' + CONVERT (VARCHAR (12),c6.[Total_payments] , 1) as [c6]

FROM Active_Loans as rptpop
LEFT JOIN payments as c1 on c1.Account = rptpop.Account and c1.pmnt_number = 'C1'
LEFT JOIN payments as c2 on c1.Account = rptpop.Account and c1.pmnt_number = 'C2'
LEFT JOIN payments as c3 on c1.Account = rptpop.Account and c1.pmnt_number = 'C3'
LEFT JOIN payments as c4 on c1.Account = rptpop.Account and c1.pmnt_number = 'C4'
LEFT JOIN payments as c5 on c1.Account = rptpop.Account and c1.pmnt_number = 'C5'
LEFT JOIN payments as c6 on c1.Account = rptpop.Account and c1.pmnt_number = 'C6'