查找日期的两个周期之间重叠的天数

我有两个表，每个表都保存日期期间（从 date1 到 date2）

我将在表1和表2中查找两个日期期间之间的重叠天数

Example

table1
-------------------------
id   |  FromDate | ToDate
1    |2000-01-01 | 2000-02-04
2    |2000-03-01 | 2000-03-29

table2
-------------------------
id   | FromDate  | ToDate
1    |2000-02-01 | 2000-02-07
2    |2000-03-27 | 2000-03-29

我想要的结果：

2000-02-01
2000-02-02
2000-02-03
2000-02-04
2000-03-27
2000-03-28
2000-03-29

这应该有效：

CREATE TABLE #t1 
(
  id int,
  FromDate date,
  ToDate date
)
CREATE TABLE #t2
(
  id int,
  FromDate date,
  ToDate date
)

INSERT #t1 VALUES 
(1, '2000-01-01', '2000-02-04'),
(2, '2000-03-01', '2000-03-29')

INSERT #t2 VALUES 
(1, '2000-02-01', '2000-02-07'),
(2, '2000-03-27', '2000-03-29')

WITH DateRange AS --select range where intersection is possible
(
    SELECT MAX(MinDate) MinDate,MIN(MaxDate) MaxDate,DATEDIFF(DAY,MAX(MinDate),MIN(MaxDate)) Diff
    FROM (VALUES ((SELECT MIN(FromDate) FROM #t1)),((SELECT MIN(FromDate) FROM #t2))) MinDate(MinDate)
    CROSS APPLY (VALUES ((SELECT MAX(ToDate) FROM #t1)),((SELECT MAX(ToDate) FROM #t2))) MaxDate(MaxDate)
), AllDates AS --generate sequence of days
(
    SELECT MinDate D, MaxDate Limit
    FROM DateRange
    UNION ALL
    SELECT DATEADD(DAY, 1, D), Limit
    FROM AllDates
    WHERE DATEADD(DAY, 1, D)<=Limit
) --select all days existing in any range in both tables
SELECT D
FROM AllDates
WHERE EXISTS (SELECT * FROM #t1 WHERE D>=FromDate AND D<=ToDate)
  AND EXISTS (SELECT * FROM #t2 WHERE D>=FromDate AND D<=ToDate)