I have the following table with the following data:
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')
How do I explode out each ActionID into 10 additional records (5 days before and 5 days after the date for current ActionID) without using slow table value function?
ActionID = 1 should explode out to 11 total records (without the time), Action 2 should explode out to 11 total records (without the time), etc
Exploded Resultset
-none
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
Asked by user2368632
(1133 rep)
Aug 6, 2021, 09:08 PM
Last activity: Aug 7, 2021, 05:08 PM
Last activity: Aug 7, 2021, 05:08 PM