Search code examples
sqlsql-servert-sqlgroupwise-maximum

Last Successful Payment Date

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.

Solution

  • 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