--CREATE TABLE Test (Id int, Date1 DATETIME, Date2 DATETIME, Value NUMERIC(12,2))

-- случайные примеры значений
/*
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (1, CAST('20180101' AS VARCHAR(8)), CAST('20180101' AS VARCHAR(8)), 10)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (2, CAST('20180106' AS VARCHAR(8)), CAST('20180107' AS VARCHAR(8)), 6)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (3, CAST('20180103' AS VARCHAR(8)), CAST('20180112' AS VARCHAR(8)), 2)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (4, CAST('20180115' AS VARCHAR(8)), CAST('20180120' AS VARCHAR(8)), 8)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (5, CAST('20180122' AS VARCHAR(8)), CAST('20180130' AS VARCHAR(8)), 12)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (6, CAST('20180128' AS VARCHAR(8)), CAST('20180215' AS VARCHAR(8)), 5)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (7, CAST('20180201' AS VARCHAR(8)), CAST('20180205' AS VARCHAR(8)), 0)

INSERT INTO Test (Id, Date1, Date2, Value) VALUES (8, CAST('20180310' AS VARCHAR(8)), CAST('20180320' AS VARCHAR(8)), 6)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (9, CAST('20180310' AS VARCHAR(8)), CAST('20180315' AS VARCHAR(8)), 3)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (10, CAST('20180324' AS VARCHAR(8)), CAST('20180330' AS VARCHAR(8)), 11)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (11, CAST('20180328' AS VARCHAR(8)), CAST('20180330' AS VARCHAR(8)), 9)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (12, CAST('20180401' AS VARCHAR(8)), CAST('20180405' AS VARCHAR(8)), 4)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (13, CAST('20180401' AS VARCHAR(8)), CAST('20180405' AS VARCHAR(8)), 6)
*/

-- Пытаюсь выделить все периоды,которые как-то пересекаются между собой следующим запросом

SELECT DISTINCT 
       CASE 
           WHEN t1.Date1 < t2.Date1 AND t1.Date2 <= t2.Date2 THEN t1.Date1
           WHEN t1.Date1 <= t2.Date1 AND t1.Date2 > t2.Date2 THEN DATEADD(d, 1, t2.Date2)
           WHEN t1.Date1 > t2.Date1 AND t1.Date2 > t2.Date2 THEN t1.Date1
           WHEN t1.Date1 >= t2.Date1 AND t1.Date2 <= t2.Date2 THEN t1.Date1
       END Date1,
       CASE 
           WHEN t1.Date1 < t2.Date1 AND t1.Date2 <= t2.Date2 THEN DATEADD(d, -1, t2.Date1)
           WHEN t1.Date1 <= t2.Date1 AND t1.Date2 > t2.Date2 THEN t1.Date2
           WHEN t1.Date1 > t2.Date1 AND t1.Date2 > t2.Date2 THEN t2.Date2
           WHEN t1.Date1 >= t2.Date1 AND t1.Date2 <= t2.Date2 THEN t1.Date2
       END Date2
FROM Test t1 INNER JOIN Test t2 ON (t1.Date1 BETWEEN t2.Date1 AND t2.Date2 OR t1.Date2 BETWEEN t2.Date1 AND t2.Date2
                                                                OR (t1.Date1 < t2.Date1 AND t1.Date2 > t2.Date2))  AND t1.Id != t2.Id
ORDER BY 1, 2

FROM Test t1 INNER JOIN Test t2 ON t1.Date1 <= t2.Date2 and t2.Date1 <= t1.Date2 and t1.Id <> t2.Id