是否有更好的方法来合并重叠的日期间隔？
我提出的解决方案非常简单,现在我想知道其他人是否能更好地了解如何做到这一点.
我提出的解决方案非常简单,现在我想知道其他人是否能更好地了解如何做到这一点.
/***** DATA EXAMPLE *****/
DECLARE @T TABLE (d1 DATETIME,d2 DATETIME)
INSERT INTO @T (d1,d2)
SELECT '2010-01-01','2010-03-31' UNION SELECT '2010-04-01','2010-05-31'
UNION SELECT '2010-06-15','2010-06-25' UNION SELECT '2010-06-26','2010-07-10'
UNION SELECT '2010-08-01','2010-08-05' UNION SELECT '2010-08-01','2010-08-09'
UNION SELECT '2010-08-02','2010-08-07' UNION SELECT '2010-08-08','2010-08-08'
UNION SELECT '2010-08-09','2010-08-12' UNION SELECT '2010-07-04','2010-08-16'
UNION SELECT '2010-11-01','2010-12-31' UNION SELECT '2010-03-01','2010-06-13'
/***** INTERVAL ANALYSIS *****/
WHILE (1=1) BEGIN
UPDATE t1 SET t1.d2 = t2.d2
FROM @T AS t1 INNER JOIN @T AS t2 ON
DATEADD(day,1,t1.d2) BETWEEN t2.d1 AND t2.d2
IF @@ROWCOUNT = 0 BREAK
END
/***** RESULT *****/
SELECT StartDate = MIN(d1),EndDate = d2
FROM @T
GROUP BY d2
ORDER BY StartDate,EndDate
/***** OUTPUT *****/
/*****
StartDate EndDate
2010-01-01 2010-06-13
2010-06-15 2010-08-16
2010-11-01 2010-12-31
*****/
解决方法
我正在寻找相同的解决方案,并在
Combine overlapping datetime to return single overlapping range record发现了这篇文章.
Packing Date Intervals还有另一个帖子.
我用各种日期范围测试了这个,包括这里列出的日期范围,每次都能正常工作.
SELECT
s1.StartDate,--t1.EndDate
MIN(t1.EndDate) AS EndDate
FROM @T s1
INNER JOIN @T t1 ON s1.StartDate <= t1.EndDate
AND NOT EXISTS(SELECT * FROM @T t2
WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate)
WHERE NOT EXISTS(SELECT * FROM @T s2
WHERE s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate)
GROUP BY s1.StartDate
ORDER BY s1.StartDate
结果是：
StartDate | EndDate 2010-01-01 | 2010-06-13 2010-06-15 | 2010-06-25 2010-06-26 | 2010-08-16 2010-11-01 | 2010-12-31
