使用 MS SQL Server 2019
我有一套經常性的捐贈記錄。每個人都有一個與之相關的第一個禮物日期和一個最后一個禮物日期。我需要在這些行中添加一個 GroupedID，以便我可以獲得最早的 FirstGiftDate 和最舊的 LastGiftDate 的完整日期范圍，只要定期捐贈之間的間隔不超過 45 天。
例如，鮑勃是長期的支持者。他的卡已經多次過期，而且他總是在 45 天內開始新的禮物。他的所有禮物都需要有一個單獨的分組 ID。在另一邊，六月一直在捐款，她的卡到期了。她在 6 個月內不再捐款，但在她的卡到期后繼續捐款。Junes的第一個禮物應該有自己的“GroupedID”，第二個和第三個應該分組在一起。分組計數應該隨著每個捐贈者重新開始。
我最初的嘗試是將捐贈表加入到別名為 D2 的自身中。這確實可以讓我知道哪些是在 45 天標記內，但我無法理解如何將它們鏈接起來。我唯一的想法是使用 LEAD 和 LAG 來嘗試分析每個場景并找出 LEAD 和 LAG 值的不同組合以使其捕捉每個不同的場景，但這似乎不像我想要的那樣可靠成為。
我感謝任何人可以提供的任何幫助。
我的代碼：
SELECT #Donation.*, D2.*
FROM #Donation
LEFT JOIN #Donation D2 ON #Donation.RecurringGiftID <> D2.RecurringGiftID
AND #Donation.Donor = D2.Donor
AND ABS(DATEDIFF(DAY, #Donation.FirstGiftDate, D2.LastGiftDate)) < 45
表結構和樣本資料：
CREATE TABLE #Donation
(
RecurringGiftID int,
Donor nvarchar(25),
FirstGiftDate date,
LastGiftDate date
)
INSERT INTO #Donation
VALUES (1, 'Bob', '2017-02-15', '2018-07-01'),
(15, 'Bob', '2018-08-05', '2019-04-01'),
(32, 'Bob', '2019-04-15', '2022-06-15'),
(54, 'June', '2015-05-01', '2016-05-01'),
(96, 'June', '2016-12-15', '2018-02-01'),
(120, 'June', '2018-03-04', '2020-07-01')
期望的輸出：
| 經常性禮物 ID | 捐贈者 | 第一個禮物日期 | 最后禮物日期 | 分組ID |
|---|---|---|---|---|
| 1 | 鮑勃 | 2017-02-15 | 2018-07-01 | 1 |
| 15 | 鮑勃 | 2018-08-05 | 2019-04-01 | 1 |
| 32 | 鮑勃 | 2019-04-15 | 2022-06-15 | 1 |
| 54 | 六月 | 2015-05-01 | 2016-05-01 | 1 |
| 96 | 六月 | 2016-12-15 | 2018-02-01 | 2 |
| 120 | 六月 | 2018-03-04 | 2020-07-01 | 2 |
uj5u.com熱心網友回復：
使用 LAG() 檢測當前行何時超過前 45 天，并執行累積和以形成所需的組 ID
select *,
GroupedID = sum(g) over (partition by Donor order by FirstGiftDate)
from
(
select *,
g = case when datediff(day,
lag(LastGiftDate, 1, '19000101') over (partition by Donor
order by FirstGiftDate),
FirstGiftDate)
> 45
then 1
else 0
end
from #Donation
) d
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514581.html
標籤：sql服务器tsql
下一篇：每天選擇平均連接數