当所有日期范围同时重叠时，获取所有重叠的日期范围

我为此苦苦挣扎了几天......尝试编写一个 SQL 查询来获取所有单位同时重叠时的所有日期范围。最好以图形方式查看。

Here is the simplified table with the image for reference:

UnitId  Start       End
======  ==========  ==========
1       05/01/2018  09/01/2018
1       10/01/2018  13/01/2018
2       04/01/2018  15/01/2018
2       19/01/2018  23/01/2018
3       06/01/2018  12/01/2018
3       14/01/2018  22/01/2018

预期结果：

Start       End
======      ========== 
06/01/2018  09/01/2018
10/01/2018  12/01/2018

我目前拥有的：

DECLARE @sourceTable TABLE (UnitId int, StartDate datetime, EndDate datetime);
INSERT INTO @sourceTable VALUES
 (1, '2018-01-05', '2018-01-09')
,(1, '2018-01-10', '2018-01-13')
,(2, '2018-01-04', '2018-01-15')
,(2, '2018-01-19', '2018-01-23')
,(3, '2018-01-06', '2018-01-12')
,(3, '2018-01-14', '2018-01-22');

SELECT DISTINCT
 (SELECT max(v) FROM (values(A.StartDate), (B.StartDate)) as value(v)) StartDate
,(SELECT min(v) FROM (values(A.EndDate), (B.EndDate)) as value(v)) EndDate
FROM @sourceTable A 
JOIN @sourceTable B 
ON A.startDate <= B.endDate AND A.endDate >= B.startDate AND A.UnitId != B.UnitId

我相信这是“计算重叠间隔的数量”问题（这张图片应该有帮助 https://i.stack.imgur.com/a8xRs.png）。这是一种解决方案：

DECLARE @t TABLE (UnitId INT, [Start] DATE, [End] DATE);
INSERT INTO @t VALUES
(1, '2018-01-05', '2018-01-09'),
(1, '2018-01-10', '2018-01-13'),
(2, '2018-01-04', '2018-01-15'),
(2, '2018-01-19', '2018-01-23'),
(3, '2018-01-06', '2018-01-12'),
(3, '2018-01-14', '2018-01-22');

WITH cte1(date, val) AS (
    SELECT [Start], 1 FROM @t AS t
    UNION ALL
    SELECT [End], 0 FROM @t AS t
    UNION ALL
    SELECT DATEADD(DAY, 1, [End]), -1 FROM @t AS t
), cte2 AS (
    SELECT date, SUM(val) OVER (ORDER BY date, val) AS usage
    FROM cte1
)
SELECT date, MAX(usage) AS usage
FROM cte2
GROUP BY date

它将为您提供使用计数（可能）发生变化的所有日期的列表：

date          usage
2018-01-04    1
2018-01-05    2
2018-01-06    3
2018-01-09    3
2018-01-10    3
2018-01-12    3
2018-01-13    2
2018-01-14    2
2018-01-15    2
2018-01-16    1
2018-01-19    2
2018-01-22    2
2018-01-23    1
2018-01-24    0

通过这种方法，您不需要日历表或 rCTE 来构建缺失的日期。将以上转换为范围（2018-01-05 ... 2018-01-15, 2018-01-19 ... 2018-01-22等）并不是很困难。

DECLARE @t TABLE (UnitId INT, [Start] DATE, [End] DATE);
INSERT INTO @t VALUES
(1, '2018-01-05', '2018-01-09'),
(1, '2018-01-10', '2018-01-13'),
(2, '2018-01-04', '2018-01-15'),
(2, '2018-01-19', '2018-01-23'),
(3, '2018-01-06', '2018-01-12'),
(3, '2018-01-14', '2018-01-22');

WITH cte1(date, val) AS (
    SELECT [Start], 1 FROM @t AS t                 -- starting date increments counter
    UNION ALL                                      
    SELECT [End], 0 FROM @t AS t                   -- we need all edges in the result
    UNION ALL                                      
    SELECT DATEADD(DAY, 1, [End]), -1 FROM @t AS t -- end date + 1 decrements counter
), cte2 AS (
    SELECT date, SUM(val) OVER (ORDER BY date, val) AS usage -- running sum for counter
    FROM cte1
), cte3 AS (
    SELECT date, MAX(usage) AS usage -- group multiple events on same date together
    FROM cte2
    GROUP BY date
), cte4 AS (
    SELECT date, usage, CASE
        WHEN usage > 1 AND LAG(usage) OVER (ORDER BY date) > 1 THEN 0
        WHEN usage < 2 AND LAG(usage) OVER (ORDER BY date) < 2 THEN 0
        ELSE 1
    END AS chg -- start new group if prev and curr usage are on opposite side of 1
    FROM cte3
), cte5 AS (
    SELECT date, usage, SUM(chg) OVER (ORDER BY date) AS grp -- number groups for each change
    FROM cte4
)
SELECT MIN(date) date1, MAX(date) date2
FROM cte5
GROUP BY grp
HAVING MIN(usage) > 1

Result:

date1         date2
2018-01-05    2018-01-15
2018-01-19    2018-01-22