SQL JOIN 查询 Last Successful Payment Date

结论

先按 applicationid + ValueDate 汇总当天的交易记录，再判断日期是否有效，最后用 MAX() 取最近的有效日期。

日期符合以下任一条件时，视为有效：

• 当天存在 Settlement；
• 当天存在 CreditAmount > 0 的 Payment，且不存在 Return 或 Refund。

WITH DailyTransactions AS
(
    SELECT
        ft.applicationid,
        CAST(ft.ValueDate AS date) AS PaymentDate,

        MAX(CASE
                WHEN ft.TransactionType = 'Payment'
                 AND COALESCE(ft.CreditAmount, 0) > 0
                THEN 1 ELSE 0
            END) AS HasPayment,

        MAX(CASE
                WHEN ft.TransactionType IN ('Return', 'Refund')
                THEN 1 ELSE 0
            END) AS HasReturnOrRefund,

        MAX(CASE
                WHEN ft.TransactionType = 'Settlement'
                THEN 1 ELSE 0
            END) AS HasSettlement
    FROM dbo.FinancialTransaction22 AS ft
    WHERE ft.TransactionType IN
          ('Payment', 'Return', 'Refund', 'Settlement')
      AND ft.Status = 'cleared'
    GROUP BY
        ft.applicationid,
        CAST(ft.ValueDate AS date)
),
LastSuccessfulPayment AS
(
    SELECT
        applicationid,
        MAX(PaymentDate) AS LastSuccessfulPaymentDate
    FROM DailyTransactions
    WHERE HasSettlement = 1
       OR (HasPayment = 1 AND HasReturnOrRefund = 0)
    GROUP BY applicationid
)
SELECT
    r.[Reference Number],
    r.Customer,
    r.Status,
    r.Amount,
    lsp.LastSuccessfulPaymentDate
FROM dbo.ReferenceTable AS r
LEFT JOIN LastSuccessfulPayment AS lsp
    ON lsp.applicationid = r.[Reference Number]
ORDER BY r.[Reference Number];

为什么要先按日期汇总

不能直接用 ROW_NUMBER() 取每个客户最新的一行，因为同一笔付款可能在同一天产生多条记录。例如：

10484 | Return  | 2022-01-06 | Debit  242.61
10484 | Payment | 2022-01-06 | Credit 242.61

如果只按日期排序，查询可能选中 Payment 行，误将 2022-01-06 认定为成功付款日期。但当天还有一条 Return，因此这一天的付款不应计入。

DailyTransactions CTE 会把同一客户在同一天的记录归为一组，并生成三个标志：

• HasPayment：当天存在金额大于零的付款入账；
• HasReturnOrRefund：当天存在退回或退款；
• HasSettlement：当天存在结算。

接着用下面的条件筛选有效日期：

HasSettlement = 1
OR (HasPayment = 1 AND HasReturnOrRefund = 0)

最后通过 MAX(PaymentDate) 得到最近一次成功付款日期。

为什么使用 LEFT JOIN

LEFT JOIN 会保留 ReferenceTable 中的所有客户。如果某个客户没有符合条件的付款，LastSuccessfulPaymentDate 将返回 NULL。

如果只想返回至少有一次成功付款的客户，可以改用：

INNER JOIN LastSuccessfulPayment AS lsp
    ON lsp.applicationid = r.[Reference Number]

需要注意的问题

1. Settlement 的拼写必须一致

示例数据中使用了：

Settlment

查询条件中使用的则是：

Settlement

如果数据库实际保存的是 Settlment，查询就无法识别这条记录。最好修正源数据。如果暂时不能修正，可以同时兼容两种拼写：

WHEN ft.TransactionType IN ('Settlement', 'Settlment')
THEN 1 ELSE 0

CTE 中的过滤条件也要相应改为：

WHERE ft.TransactionType IN
      ('Payment', 'Return', 'Refund', 'Settlement', 'Settlment')

2. 成功付款要求 CreditAmount > 0

查询并非只根据 TransactionType = 'Payment' 判断付款是否成功，还要求：

COALESCE(ft.CreditAmount, 0) > 0

因此，如果一条 Payment 记录只有 DebitAmount，没有有效的 CreditAmount，它不会被认定为成功付款。

3. 跨日期退款需要额外的关联字段

这段查询严格按照“同一 applicationid、同一日期出现 Return 或 Refund”的规则处理。

如果退款可能在付款后的其他日期发生，只根据 applicationid、日期和金额，无法可靠判断退款对应哪一笔付款。这时需要付款交易 ID、原始交易 ID 或退款关联 ID，例如：

TransactionId
OriginalTransactionId

查询应根据这些字段的关联关系排除被退款的原始付款，不能只比较日期或金额。

按照示例中的规则，查询结果为：

备注：内容仅供参考。