Explode a date range

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply