首先,我向整个表添加了行号。
WITH RowN AS
(
SELECT Date, Name, ROW_NUMBER() OVER (ORDER BY Name, Date) RN
FROM #T
)
然后我将这个表与其本身连接起来只是为了计算日期之间的天数。
,CalcDiffDays AS
(
SELECT RowN.Date, RowN.Name,
ISLAND = RowN.Name +
CASE
WHEN RowN.RN > 1 AND RowN.Name = R2.Name THEN CAST(DATEDIFF(day, R2.Date, RowN.Date) AS VARCHAR(20))
ELSE '1'
END
FROM RowN
LEFT JOIN RowN R2 ON R2.RN = RowN.RN-1
)
差距。同名的连续日期之间有多少天。
岛屿。通过将名称添加到计算的天数中。
+---------------------+------+---------+
| Date | Name | NumDays |
+---------------------+------+---------+
| 07.01.2017 00:00:00 | A | A1 |
+---------------------+------+---------+
| 08.01.2017 00:00:00 | A | A1 |
+---------------------+------+---------+
| 09.01.2017 00:00:00 | A | A1 |
+---------------------+------+---------+
| 12.01.2017 00:00:00 | A | A3 |
+---------------------+------+---------+
| 07.01.2017 00:00:00 | B | B1 |
+---------------------+------+---------+
| 08.01.2017 00:00:00 | B | B1 |
+---------------------+------+---------+
| 09.01.2017 00:00:00 | B | B1 |
+---------------------+------+---------+
第二部分:获取每个岛屿的最小和最大日期。
WITH RowN AS
(
SELECT Date, Name, ROW_NUMBER() OVER (ORDER BY Name, Date) RN
FROM #T
)
,CalcDiffDays AS
(
SELECT RowN.Date, RowN.Name,
ISLAND = RowN.Name +
CASE
WHEN RowN.RN > 1 AND RowN.Name = R2.Name THEN CAST(DATEDIFF(day, R2.Date, RowN.Date) AS VARCHAR(20))
ELSE '1'
END
FROM RowN
LEFT JOIN RowN R2 ON R2.RN = RowN.RN-1
)
SELECT CONVERT(VARCHAR(20), MIN(Date), 102) + ' - ' + CONVERT(VARCHAR(20), MAX(Date), 102) AS [Data Range], Name
FROM CalcDiffDays
GROUP BY ISLAND, Name
ORDER BY MIN(Date);
+-------------------------+------+
| Data Range | Name |
+-------------------------+------+
| 2017.01.07 - 2017.01.09 | A |
+-------------------------+------+
| 2017.01.07 - 2017.01.09 | B |
+-------------------------+------+
| 2017.01.12 - 2017.01.12 | A |
+-------------------------+------+
可以在这里检查:http://rextester.com/MHLEEJ50479 http://rextester.com/MHLEEJ50479