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

本文介绍了查找两个日期时间段之间的重叠天数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个表，每个表都保存日期的时间段(从 date1 到 date2)

I have two tables, each of which holds the period of dates (from date1 to date2)

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

i will Find overlapping days between two periods of Date in table1 and table2

示例

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

推荐答案

使用 CTE 和递归可以做到这一点.

It's possible to do this with CTE's and recursion.

--Your sample data DECLARE @table1 TABLE (id int PRIMARY KEY, FromDate date, ToDate date) DECLARE @table2 TABLE (id int PRIMARY KEY, FromDate date, ToDate date) INSERT INTO @table1 VALUES (1, '2000-01-01', '2000-02-04') , (2, '2000-03-01', '2000-03-29') INSERT INTO @table2 VALUES (1, '2000-02-01', '2000-02-07') , (2, '2000-03-27', '2000-03-29') --A couple CTE's ;WITH cteDates AS ( SELECT T1.id --get the min and max dates for each id ,CASE WHEN T1.FromDate > T2.FromDate THEN T1.FromDate ELSE T2.FromDate END [mindate] ,CASE WHEN T1.ToDate < T2.ToDate THEN T1.ToDate ELSE T2.ToDate END [maxdate] FROM @table1 T1 INNER JOIN @table2 T2 ON T1.id = T2.id ) , cteRecursion AS ( --date range for each id SELECT id, mindate AS DateValue FROM cteDates UNION ALL SELECT id, DATEADD(DAY, 1, DateValue) FROM cteRecursion C1 WHERE DATEADD(DAY, 1, DateValue) <= ( SELECT maxDate FROM cteDates C2 WHERE C2.id = C1.id ) ) --SELECT query SELECT DateValue FROM cteRecursion ORDER BY DateValue OPTION (MAXRECURSION 0)

产生输出:

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