Last Successful Payment Date

Question

How I can join the below two tables and get only the LastSucessfull Payment Date from the Transactions Table? I want to pull only LastSucesfullPaymentdates, which should also consider through the returns;

Business Rules for LastSucesfull Payment Date:

1. If a recent payment shows as Return or refund, it will show in Debit AMount, and Transaction Type is 'Return' or 'Refund'. It will have two entries with the same Date One as Debitamount because of return and Creditamount because we attempted to collect the amount. Then this scenario should consider the previous successful payment date.

2. If it is Successful without any returns on the same date, It will show in Creditamount and Transaction Type is 'Payment'. This will be the last Successful Payment Date

3. If the TransactionType is Settlement- This will Last Successful Payment Date

At present, this is the query that I use for above output:

Below is the Transactionaltable

---

Reference Number	PaymentNumber	TransactionType	Date	DebitAmount	CreditAMount
10484	1	Return	06/01/2022	242.61
10484	2	Payment	06/01/2022		242.61
10484	3	Payment	06/12/2021		242.61
10484	4	Payment	08/11/2021		242.61
10484	5	Payment	06/11/2021		242.61
10559	1	Payment	13/01/2022	0	529.65
10559	2	Return	10/01/2022	529.65
10559	3	Payment	10/01/2022		529.65
10559	4	Payment	10/12/2021		529.65
10598	1	Refund	29/12/2020	121.31
10598	2	Payment	11/12/2020		121.31
37473	1	Payment	22/01/2022	0	116.08
37473	2	Payment	22/12/2021	116.08
37473	3	Payment	22/11/2021		116.08
37466	1	Settlment	28/01/2022		1300
37466	2	Payment	28/12/2021		127.00
37466	3	Payment	28/11/2021		127.00
37466	4	Payment	28/10/2021		127.00

SELECT 
    ft.applicationid  as 'Reference Number',
    ROW_NUMBER() OVER (PARTITION BY ft.applicationid ORDER BY ft.valueDate DESC) AS PaymentNumber,
    ft.[TransactionType],
    CAST(Valuedate AS DATE) as 'Date',
    ft.debitamount AS DebitAmount,
    ft.creditamount AS CreditAMount 
FROM  dbo.FinancialTransaction22 as ft 
WHERE ft.[TransactionType] in ('Payment','Return', 'Settlement', 'Refund') and ft.[Status]='cleared

Below is all RefernceTable

---

Reference Number	Customer	Status	Amount
10484	Glen	Active	12000
10559	Nyame	Active	5000
10598	Philip	Complete	6000
37473	Natalie	Active	6000
37466	Charlotte	Active	20000

At present, this is the query that I use for Referencetable:

Select Reference Number, Customer, Status, Amount from Reference table

I'm looking New table having LastSucessfullPayment column I'm a beginner in SQL. However, I'm trying to achieve the below output, and I have manually added the 'Last Successful Payment Date' Date as per the above 3 Business rules I used from Transaction Table.

My Desired Output as below

Reference Number	Customer	Status	Amount	LastSucessfullPaymetDatet
10484	Glen	Active	12000	06/12/2021
10559	Nyame	Active	5000	13/01/2022
10598	Philip	Complete	6000	11/12/2021
37473	Natalie	Active	6000	22/01/2022
37466	Charlotte	Active	20000	28/01/2022

Thanks for Support.

Answer 1

Basically the query is using APPLY() operator to get 1 transaction for each of the Reference Number.

For Business Rule 1, this is handle by checking for following express is greater than 0

SUM(ISNULL(creditamount, 0) - ISNULL(debitamount, 0)) OVER (PARTITION BY Valuedate)

For Business Rule 2 & 3, CASE WHEN TransactionType = 'Settlement' will gives Settlement transaction lower ROW_NUMBER() value. The rest of transaction is ORDER BY valueDate DESC

ROW_NUMBER() OVER (ORDER BY CASE WHEN TransactionType = 'Settlement' THEN 1 ELSE 2 END,
                            ft.valueDate DESC) 

---

The query:

SELECT r.[Reference Number], r.Customer, r.Status, r.Amount, d.LastSucessfullPaymetDatet
FROM   Reference r
       CROSS APPLY
       (
           SELECT TOP 1 LastSucessfullPaymetDatet = Valuedate
           FROM
           (
               SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN TransactionType = 'Settlement' 
                                                       THEN 1 
                                                       ELSE 2 
                                                       END,
                                                  ft.valueDate DESC) AS PaymentNumber,
                      ft.Valuedate,
                      SUM(ISNULL(ft.creditamount, 0) - ISNULL(ft.debitamount, 0))
                      OVER (PARTITION BY ft.Valuedate) AS NettValueByDate
               FROM   FinancialTransaction22 ft
               WHERE  ft.applicationid = r.[Reference Number]
               AND    ft.[TransactionType] in ('Payment','Return', 'Settlement', 'Refund') 
               AND    ft.[Status] = 'cleared'
           ) d
           WHERE NettValueByDate> 0
           ORDER BY PaymentNumber
       ) d