Explode a date range

Question

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

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

score 6 · Accepted Answer · answered Aug 07 '21 at 16:02

Cross join each source row with each of the 11 offset day values, removing the time via a conversion to date:

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<>fiddle demo

score 2 · Answer 2 · edited Aug 07 '21 at 17:08

2

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?

Create a calendar table and join that.

edited Aug 07 '21 at 17:08

Erik Reasonable Rates Darling

45,549
14
145
532

answered Aug 07 '21 at 15:47

David Browne - Microsoft

49,000
3
53
102

Explode a date range

2 Answers2