回想距離上次鐵人賽到現在已經快一年了
後來工作經常用到蠻多 CTE 遞迴查詢
剛好在新的鐵人賽開始前另外筆記一下最近遇到蠻好用的需求：取得多組日期區間內的所有日期
可惜找不到方法補充在原系列文

一、最近工作遇到的需求實例

有一個活動資料表，有三個欄位：活動名稱、活動開始日、活動結束日
活動頁面有一個行事曆，需要標示某個日期區間裡，有活動的所有日期

例如活動資料表當中，八月有這些活動：

八月期間，所有需要標示有舉辦活動的日期：

二、SQL

playground

1.表結構與資料

CREATE TABLE Item (
  Item_No int PRIMARY KEY IDENTITY ,
  ActivityName NVARCHAR(200),
  StartDate DATETIME,
  EndDate DATETIME,
)

INSERT INTO Item 
(ActivityName, StartDate, EndDate)
VALUES
('test01', '2020-08-05', '2020-08-09'),
('test02', '2020-08-07', '2020-08-11'),
('test03', '2020-07-29', '2020-08-03'),
('test04', '2020-08-29', '2020-09-03'),
('test05', '2020-09-10', '2020-09-13'),
('test06', '2020-08-14', '2020-08-16')

2.取得八月所有活動，舉辦的所有日期

WITH dates AS (
        SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
        FROM Item
        WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
              (EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
        UNION ALL
        SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
        FROM dates
        WHERE Date < EndDate
) 

SELECT ActivityName, 
       CAST([Date] AS DATE) AS [Date], 
       CAST(StartDate AS DATE) AS StartDate,
       CAST(EndDate AS DATE) AS EndDate
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY StartDate, [Date];

3. 取得八月所有舉辦活動的日期

WITH dates AS (
        SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
        FROM Item
        WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
              (EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
        UNION ALL
        SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
        FROM dates
        WHERE Date < EndDate
) 
SELECT DISTINCT CAST([Date] AS DATE) AS [Date]
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY [Date]

三、CTE 遞迴查詢結構

WITH recursive_sql (column_list)
AS
(
    -- Anchor member
    初始化 SELECT ...
    UNION ALL
    -- Recursive member that references recursive_sql.
    遞迴查詢 SELECT...FROM recursive_sql
)

-- 呼叫遞迴查詢
SELECT *
FROM   recursive_sql

四、說明

1. 第一次呼叫遞迴查詢時，會先執行上方的初始化 SELECT，取得所有的活動資料
2. 接著將初始化 SELECT得到的資料，將活動開始日+1代入遞迴查詢 SELECT，開始遞迴查詢
3. 直到[Date] = DATEADD(DAY, 1, [Date]) 帶入遞迴查詢 SELECT時，[Date] < EndDate，遞迴查詢結果為空，結束遞迴查詢

如果其他大大有更好的寫法，歡迎指教與提出建議