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

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

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

Thanks for Support.

CodePudding user response：

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