Finding the last 6 months payments, using a partitioning scheme in Microsoft sql server

Question

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

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'
)

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%'

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.

Edit:

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'
)
,

But I get this error

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 ','.

Edit

To make this really clear this is the output that I should get:

user_4959221 · Answer

This is too long for a comment

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

when you have two payments for the same Account, Total, DateRec and SourceTyp, you take it as one payment? That does not seem right. Also, if you have ties on DateRec for some Account, the ROW_NUMBER() assignment for the ties is not deterministic. And that may be a problem, because you evaluate the CTE six times and you may end up with duplicate or missing values in the result. You should add columns to the ORDER BY to make it deterministic.

For the question, maybe another CTE?

get_payment6sum as
(
SELECT sum(Total) as Last6PaymentsSum, Account
FROM payments
WHERE RowNumber <= 6 AND SourceTyp = 'RegPmt'
GROUP BY Account
)

user_11504128 · Answer

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

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.

WITH PIVOT OPTION

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

WITH LEFT JOIN

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'

Finding the last 6 months payments, using a partitioning scheme in Microsoft sql server

2 Answers