如何加入以下兩個表并從交易表中僅獲取 LastSucessfull 付款日期？我只想提取 LastSucesfullPaymentdates，這也應該通過回報來考慮；
LastSucesfull付款日業務規則：
如果最近的付款顯示為退貨或退款，它將顯示在借方金額中，并且交易型別為“退貨”或“退款”。它將有兩個條目，其中一個日期為 Debitamount 是因為 return 和 Creditamount，因為我們試圖收取金額。那么這個場景應該考慮之前的成功支付日期。
如果在同一天沒有任何退貨成功，它將顯示在 Creditamount 和 Transaction Type 是'Payment'。這將是最后一個成功的付款日期
如果 TransactionType 是 Settlement - 這將是最后一次成功的付款日期
目前，這是我用于上述輸出的查詢：
下面是Transactionaltable
| 參考編號 | 付款號碼 | 交易型別 | 日期 | 借記金額 | 信用金額 |
|---|---|---|---|---|---|
| 10484 | 1 | 回傳 | 2022 年 6 月 1 日 | 242.61 | |
| 10484 | 2 | 支付 | 2022 年 6 月 1 日 | 242.61 | |
| 10484 | 3 | 支付 | 2021 年 6 月 12 日 | 242.61 | |
| 10484 | 4 | 支付 | 2021 年 8 月 11 日 | 242.61 | |
| 10484 | 5 | 支付 | 2021 年 6 月 11 日 | 242.61 | |
| 10559 | 1 | 支付 | 2022 年 13 月 1 日 | 0 | 529.65 |
| 10559 | 2 | 回傳 | 2022 年 10 月 1 日 | 529.65 | |
| 10559 | 3 | 支付 | 2022 年 10 月 1 日 | 529.65 | |
| 10559 | 4 | 支付 | 2021 年 10 月 12 日 | 529.65 | |
| 10598 | 1 | 退款 | 2020 年 12 月 29 日 | 121.31 | |
| 10598 | 2 | 支付 | 2020 年 11 月 12 日 | 121.31 | |
| 37473 | 1 | 支付 | 22/01/2022 | 0 | 116.08 |
| 37473 | 2 | 支付 | 22/12/2021 | 116.08 | |
| 37473 | 3 | 支付 | 22/11/2021 | 116.08 | |
| 37466 | 1 | 結算 | 28/01/2022 | 1300 | |
| 37466 | 2 | 支付 | 28/12/2021 | 127.00 | |
| 37466 | 3 | 支付 | 28/11/2021 | 127.00 | |
| 37466 | 4 | 支付 | 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
以下是所有參考表
| 參考編號 | 顧客 | 狀態 | 數量 |
|---|---|---|---|
| 10484 | 格倫 | 積極的 | 12000 |
| 10559 | 尼亞姆 | 積極的 | 5000 |
| 10598 | 菲利普 | 完全的 | 6000 |
| 37473 | 娜塔莉 | 積極的 | 6000 |
| 37466 | 夏洛特 | 積極的 | 20000 |
目前，這是我用于 Referencetable 的查詢：
Select Reference Number, Customer, Status, Amount from Reference table
我正在尋找具有 LastSucessfullPayment 列的新表 我是 SQL 的初學者。但是，我正在嘗試實作以下輸出，并且根據我在交易表中使用的上述 3 個業務規則，我手動添加了“最后成功付款日期”日期。
我想要的輸出如下
| 參考編號 | 顧客 | 狀態 | 數量 | LastSucessfullPaymet日期 |
|---|---|---|---|---|
| 10484 | 格倫 | 積極的 | 12000 | 2021 年 6 月 12 日 |
| 10559 | 尼亞姆 | 積極的 | 5000 | 2022 年 13 月 1 日 |
| 10598 | 菲利普 | 完全的 | 6000 | 2021 年 11 月 12 日 |
| 37473 | 娜塔莉 | 積極的 | 6000 | 22/01/2022 |
| 37466 | 夏洛特 | 積極的 | 20000 | 28/01/2022 |
謝謝你的支持。
uj5u.com熱心網友回復：
基本上，查詢使用APPLY()運算子為每個Reference Number.
對于業務規則 1，這是通過檢查以下 express 是否大于 0 來處理的
SUM(ISNULL(creditamount, 0) - ISNULL(debitamount, 0)) OVER (PARTITION BY Valuedate)
對于業務規則 2 和 3，CASE WHEN TransactionType = 'Settlement'將賦予Settlement交易較低的ROW_NUMBER()價值。剩下的交易是ORDER BY valueDate DESC
ROW_NUMBER() OVER (ORDER BY CASE WHEN TransactionType = 'Settlement' THEN 1 ELSE 2 END,
ft.valueDate DESC)
查詢：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/431803.html