T-SQL对动态日期范围进行分组

Question

使用MS SQL Server 2019

我有一套经常捐款的记录。每个礼物都有一个与其关联的第一次礼物日期和最后一次礼物日期。我需要在这些行中添加一个GroupedID，这样我就可以获得最早的FirstGiftDate和最早的LastGiftDate的完整日期范围，只要经常捐款之间的间隔不超过45天。

例如，鲍勃是一个长期的支持者。他的卡已经多次过期，他总是在45天内开始赠送新礼物。他所有的礼物都需要一个单独的分组ID。在另一边，June一直在捐款，她的卡过期了。她有6个月没有再捐款了，但在信用卡到期后继续捐款。Junes的第一份礼物应该有它自己的"GroupedID"；第二个和第三个应该分组在一起。分组计数应针对每个供体重新开始。

我最初的尝试是加入捐赠表，重新命名为D2。这确实给了我一个指标，让我知道哪些在45天内，但我无法理解如何将它们联系起来。我唯一的想法是使用LEAD和LAG来尝试分析每个场景，并找出捕获每个不同场景所需的LEAD和LA值的不同组合，但这似乎不像我希望的那样可靠

我感谢任何人能提供的帮助。

我的代码：

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')

期望输出：

FirstGiftDate[/tr><1>2018-08-05

RecuringGiftId	捐赠者	LastGiftDate		GroupedID
1	Bob	2017-02-15	2018-07-01
15	Bob	2019-04-01	1
32	Bob	2019-04-15	2022-06-15	1
54	6月	2015-05-01	2016-05-01	1
96	6月	2016-12-15	2018年02月01日	2
120	6月	2018年03月04日	2020-07-01	2

使用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