我想要做的是为连续日期范围创建分组序列。取以下样本数据:
Person|BeginDate |EndDate
A |1/1/2015 |1/31/2015
A |2/1/2015 |2/28/2015
A |4/1/2015 |4/30/2015
A |5/1/2015 |5/31/2015
B |1/1/2015 |1/30/2015
B |8/1/2015 |8/30/2015
B |9/1/2015 |9/30/2015
如果当前行中的 BeginDate 距上一行中的 EndDate >1 天,则将计数器增加 1,否则分配计数器的当前值。排序如下:
Person|BeginDate |EndDate |Sequence
A |1/1/2015 |1/31/2015|1
A |2/1/2015 |2/28/2015|1
A |4/1/2015 |4/30/2015|2
A |5/1/2015 |5/31/2015|2
B |1/1/2015 |1/30/2015|1
B |8/1/2015 |8/30/2015|2
B |9/1/2015 |9/30/2015|2
为每个人进行分区和重置。
为了您的测试:
CREATE TABLE ##SequencingTest(
Person char(1)
,BeginDate date
,EndDate date)
INSERT INTO ##SequencingTest
VALUES
('A','1/1/2015','1/31/2015')
,('A','2/1/2015','2/28/2015')
,('A','4/1/2015','4/30/2015')
,('A','5/1/2015','5/31/2015')
,('B','1/1/2015','1/30/2015')
,('B','8/15/2015','8/31/2015')
,('B','9/1/2015','9/30/2015')