Get all overlapping date ranges when all overlap at the same time

Question

I'm struggling with this for a few days... trying to write an SQL query to get all date ranges when all units overlap at the same time. It's better to see it graphically.

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

Expected result:

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

What I currently have:

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

Answer 1

I believe it is "count number of overlapping intervals" problem (this picture should help). Here is one solution to it:

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

It will give you a list of all dates at which the use count (possibly) changed:

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

With this approach you do not need a calendar table or rCTE to build missing dates. Converting the above to ranges (2018-01-05 ... 2018-01-15, 2018-01-19 ... 2018-01-22 etc) is not very difficult.

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