我需要对 SQL Server 2012 中的岛屿进行编号。岛屿被定义为一组行,其中之间没有天数间隔DateFrom
and DateTo
同一内ItemId
).
以下数据集:
CREATE TABLE #Ranges (ItemId INT, DateFrom DATETIME, DateTo DATETIME)
INSERT INTO #Ranges VALUES (1,'2015-01-31','2015-02-17')
INSERT INTO #Ranges VALUES (1,'2015-02-18','2015-03-31')
INSERT INTO #Ranges VALUES (1,'2015-04-14','2015-05-21')
INSERT INTO #Ranges VALUES (2,'2015-07-12','2015-07-19')
INSERT INTO #Ranges VALUES (2,'2015-07-20','2015-07-24')
INSERT INTO #Ranges VALUES (2,'2015-07-26','2015-08-02')
INSERT INTO #Ranges VALUES (2,'2015-08-03','2015-08-07')
应编号如下:
ItemId; DateFrom; DateTo; Number
1; 2015-01-31; 2015-02-17; 1
1; 2015-02-18; 2015-03-31; 1
1; 2015-04-14; 2015-05-21; 2
2; 2015-07-12; 2015-07-19; 3
2; 2015-07-20; 2015-07-24; 3
2; 2015-07-26; 2015-08-02; 4
2; 2015-08-03; 2015-08-07; 4
非常感谢任何帮助。
问候,
普热梅克