展開日期範圍

我有下表，其中包含以下數據：

DECLARE @MyActions TABLE (ActionId INT NOT NULL, ActionDate DATETIME NOT NULL)

INSERT INTO @MyActions VALUES (1, '2021-08-01 01:00:00')
INSERT INTO @MyActions VALUES (2, '2021-08-02 02:00:00')
INSERT INTO @MyActions VALUES (3, '2021-08-03 03:00:00')
INSERT INTO @MyActions VALUES (4, '2021-08-04 04:00:00')

如何在不使用慢表值函式的情況下將每個 ActionID 分解為 10 條附加記錄（目前 ActionID 日期的前 5 天和後 5 天）？

ActionID = 1 應該擴展到 11 條記錄（不包括時間），Action 2 應該擴展到 11 條記錄（不包括時間），等等

展開的結果集

1   2021-07-27 00:00:00.000
1   2021-07-28 00:00:00.000
1   2021-07-29 00:00:00.000
1   2021-07-30 00:00:00.000
1   2021-07-31 00:00:00.000
1   2021-08-01 00:00:00.000
1   2021-08-02 00:00:00.000
1   2021-08-03 00:00:00.000
1   2021-08-04 00:00:00.000
1   2021-08-05 00:00:00.000
1   2021-08-06 00:00:00.000
2   2021-07-28 00:00:00.000
2   2021-07-29 00:00:00.000
2   2021-07-30 00:00:00.000
2   2021-07-31 00:00:00.000
2   2021-08-01 00:00:00.000
2   2021-08-02 00:00:00.000
2   2021-08-03 00:00:00.000
2   2021-08-04 00:00:00.000
2   2021-08-05 00:00:00.000
2   2021-08-06 00:00:00.000
2   2021-08-07 00:00:00.000
3   2021-07-29 00:00:00.000
3   2021-07-30 00:00:00.000
3   2021-07-31 00:00:00.000
3   2021-08-01 00:00:00.000
3   2021-08-02 00:00:00.000
3   2021-08-03 00:00:00.000
3   2021-08-04 00:00:00.000
3   2021-08-05 00:00:00.000
3   2021-08-06 00:00:00.000
3   2021-08-07 00:00:00.000
3   2021-08-08 00:00:00.000
4   2021-07-30 00:00:00.000
4   2021-07-31 00:00:00.000
4   2021-08-01 00:00:00.000
4   2021-08-02 00:00:00.000
4   2021-08-03 00:00:00.000
4   2021-08-04 00:00:00.000
4   2021-08-05 00:00:00.000
4   2021-08-06 00:00:00.000
4   2021-08-07 00:00:00.000
4   2021-08-08 00:00:00.000
4   2021-08-09 00:00:00.000

將每個源行與 11 個偏移日值中的每一個交叉連接，通過轉換為日期來刪除時間：

SELECT
   MA.ActionId, 
   ActionDate = 
       CONVERT(datetime, 
           CONVERT(date, 
               DATEADD(DAY, Offset.d, MA.ActionDate)))
FROM @MyActions AS MA
CROSS JOIN 
(
   VALUES
       (-5), (-4), (-3), (-2), (-1), (0),
       (+1), (+2), (+3), (+4), (+5)
) AS Offset (d);

db<>小提琴展示

如何在不使用慢表值函式的情況下將每個 ActionID 分解為 10 條附加記錄（目前 ActionID 日期的前 5 天和後 5 天）？

創建一個日曆表並加入它。

引用自：https://dba.stackexchange.com/questions/297707