使用Microsoft SQL Server中的分区方案查找最近6个月的付款 [英] Finding the last 6 months payments, using a partitioning scheme in Microsoft sql server
问题描述
This is a follow up from this post. What I am trying to do now is sum the total payments made for the last 6 months. For example, we have this loan
如您所见，他们在4月份支付了3笔款项，我需要做的就是将这些款项相加得出净额.目前，我的查询只是找到其中之一并采用了其中之一，但这是不正确的.我试图做的是这样:
as you can see they made 3 payments in the month of April, what I need to do is sum those to get the net amount. Currently my query just finds one of them and takes that one but that is not correct. What I tried to do is this:
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指定的当月所有付款的总和.我不确定现在要做什么才能达到目的.
But for the loan above I only get the negative value and what I need is the sum of all the payments made for the month which is specified by DateRec. I am unsure what to do at this point to achieve the result.
这是我到目前为止的全部查询:
Here is my entire query I have so far:
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个月内支付的所有款项，我认为只需要对我在上一篇文章中所做的稍作修改即可.
If something was not clear with what I was asking please let me know. Again I just want to create a way to find all payments made in 6 months for each loan, I would assume it just needs to be a slight modification that was done in the previous post I made.
我也尝试过这样的事情:
I have tried something like this as well:
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 ','.
修改
要弄清楚这是我应该得到的输出:
To make this really clear this is the output that I should get:
推荐答案
您应该首先汇总每个月的付款，然后根据需要为它们分配row_numbers或最近6个月的过滤条件.您可以使用下面的伪代码获得最近6笔付款.
You should start with summarizing each months payment and assign them row_numbers or filter on last 6 months depending on requirement. You can get last 6 payments using the pseudo code below.
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.
Once you have your last 6 months payments for your accounts with other attributes then you can do a pivot to get an output like you mentioned or you can also use the left join approach but you can simply use payments CTE instead of creating 6 CTEs.
具有PIVOT选项
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'
这篇关于使用Microsoft SQL Server中的分区方案查找最近6个月的付款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
