問題描述

我有兩個表，每個表都保存日期的時間段(從 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

這篇關于查找兩個日期時間段之間的重疊天數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！