为24笔贷款追加最后6笔付款

Question

我正在Microsoft sql server中构建一个查询，在该查询中，我希望找到活动贷款以及有关它们的一些信息。我已经构建了一个通用的表表达式，给出了正确的数据。现在我需要从今天开始得到最后6笔付款。我有另一个常见的表格表达式，它给出了所有的付款和收到的付款数据，但我不知道如何将最近的6项付款作为支点和附加内容，这样我就有了如下内容：

​

​

这是常见表表达式的查询和输出，我可以在其中获得c1、.、c6。

SELECT Account,Total,CONVERT(datetime,DateRec)  [Date Received]
FROM mars.dbo.vw_PaymentHistory PH 
WHERE  PH.SourceTyp not like '%fundin%' and PH.SourceTyp not like '%draw%'

这给出了这个(但更多)：

​

​

下面是我正在处理的整个查询：

Declare @monthEnding date = '3/31/2020',
        @monthStart date = '3/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_vw_Loans_ArchiveEOM 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.MonthEnding = @monthEnding
    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_vw_Loans_ArchiveEOM 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 < @monthEnding
    AND l.PaidOffDate > @monthEnding
    AND la.MonthEnding = @monthEnding
    AND la.LoanStatus NOT like 'trailing claims'
    AND la.LoanStatus NOT like 'Inactive - REO/FC'
    AND pt.[Partition] IS NOT NULL
)
)
,

payments as
(
SELECT Account,Total,CONVERT(datetime,DateRec)  [Date Received]
FROM mars.dbo.vw_PaymentHistory PH 
WHERE  PH.SourceTyp not like '%fundin%' and PH.SourceTyp not like '%draw%'
)





SELECT 

rptpop.Account
, 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]


FROM Active_Loans as rptpop
LEFT JOIN payments as pmt on pmt.Account = rptpop.Account

WHERE 
rptpop.Partition  = 'GAEA'
AND rptpop.LoanStatus = 'Current'
AND rptpop.[Last Name] NOT LIKE '%CRE%'
AND pmt.[Date Received] BETWEEN @monthStart AND @monthEnding

编辑：

根据下面的答案，我做到了这一点：

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

get_payment1 as
(
SELECT * FROM payments
where RowNumber = 1

)

这给了我数字，但我不明白的是，是否1.)这确实是正确的，2.)假设它是正确的，它是如何得到最近的日期？也许是按条款下的订单？

Answer 1

我看到了解决这个问题的几种方法。我可以使用伪代码共享这个方法。

创建支付CTE，类似于SELECT ROW_NUMBER() OVER(Partition By Account ORDER BY ReceivedDate DESC)。然后在Row_Number 1到6之间创建使用上述CTE的6 CTE，然后简单地使用那些在查询中使用左联接的CTE，加入Account#。这将添加c1-c6列。

第二种方法可以是使用相同的支付unpivot (Amount for Rows in (1,2,3,4,5,6) ) u;，但不要使用多个CTE，而要找到一种使用UNPIVOT的方法。