旋转附加 24 笔贷款的最后 6 笔付款 [英] Pivoting to append the last 6 payments for 24 loans

问题描述

我正在 Microsoft sql server 中构建查询，我想在其中查找活动贷款以及有关它们的一些信息.我已经构建了一个通用表表达式，可以为我提供正确的人口.现在我需要从今天开始收到最后 6 笔付款.我有另一个通用表表达式，它给出了所有付款和收到的付款数据，但我不确定如何旋转和附加最后 6 笔付款，以便我有这样的东西:

I am building a query in Microsoft sql server where I want to find the active loans as well as some information on them. I have built out a common table expression that gives me the correct population. Now I need to get the last 6 payments starting from today. I have a another common table expression that gives all the payments and the payment data received but I am not sure how to pivot and append the last 6 payments such that I have something like this:

这是我可以得到 c1,...,c6 的公共表表达式的查询和输出的样子.

This is what the query and output looks like for the common table expression where I can get 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%'

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

which gives this (but much more):

这是我正在使用的整个查询:

Here is the whole query I am working from:

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

根据下面的答案，我能够做到这一点:

Based on the answer below I was able to do this:

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.) 假设它是正确的，它如何获得最近的日期?也许是 order by 子句?

Which gives me numbers but what I do not understand is whether 1.) This is indeed correct and 2.) Assuming it is correct how do is it getting the most recent date? Perhaps its the order by clause?

推荐答案

我看到了几种解决问题的方法.我可以使用伪代码分享该方法.

I see a couple of way to solve the problem. I can share the approach using pseudo code.

创建付款 CTE ，类似于 SELECT ROW_NUMBER() OVER(Partition By Account ORDER BY ReceivedDate DESC) .然后创建 6 个使用上述 CTE 和 Row_Number 1 到 6 的 CTE.然后只需在查询中使用那些使用 Left Join 的，加入 Account#.这将添加 c1-c6 列.

Create Payments CTE , something like SELECT ROW_NUMBER() OVER(Partition By Account ORDER BY ReceivedDate DESC) . Then create 6 CTE's that use the above CTE with Row_Number 1 thru 6. Then simply use those using Left Join in your query, joining on Account#. This will add c1-c6 columns.

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

A second approach can be to use the same Payments CTE but instead of multiple CTEs, find a way to use UNPIVOT unpivot (Amount for Rows in (1,2,3,4,5,6) ) u;

这篇关于旋转附加 24 笔贷款的最后 6 笔付款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！