SQL recursion and cte - dates timeseries

Question

I need to build vector(s) of time series dates (say 10) for a subset of start dates.

The following statement returns 10 consecutive dates starting from '2010-01-01'.

;with cte as
(select 1 i union all
 select i+1 i from cte where i < 10)
select '2010-01-01' as start_date, dateadd(d, i-1, '2010-01-01') AS trial_date
INTO #tmp_trialdates
FROM cte

Does anyone have any suggestions as to how I could incorporate querying a table (or other dataset) of multiple start dates (not necessarily sequential), generating time series dates for each start date, then populating the data in the one output table?

Answer 1

For this you need to change the anchor set of the recursive CTE, so each row starts with the dates from the other table before starting the recursive portion.

DECLARE @AmountDays INT = 10

;WITH GeneratedDays AS
(
    -- Anchor
    SELECT
        DateSeriesIdentifier = T.DateSeriesIdentifier,
        Date = T.Date, -- Assuming it's DATE data type
        RecursionLevel = 1
    FROM
        OtherTable AS T

    UNION ALL

    -- Recursion
    SELECT
        DateSeriesIdentifier = G.DateSeriesIdentifier,
        Date = DATEADD(DAY, 1, G.Date),
        RecursionLevel = G.RecursionLevel + 1
    FROM
        GeneratedDays AS G
    WHERE
        G.RecursionLevel + 1 <= @AmountDays
)
SELECT
    G.DateSeriesIdentifier,
    G.Date,
    G.RecursionLevel
FROM
    GeneratedDays AS G
ORDER BY
    G.DateSeriesIdentifier,
    G.RecursionLevel

This is a result from a set I used to test which had dates 2016-02-04, 2018-01-19 and 2018-08-30:

DateSeriesIdentifier    Date            RecursionLevel
21                      2016-02-04      1
21                      2016-02-05      2
21                      2016-02-06      3
21                      2016-02-07      4
21                      2016-02-08      5
21                      2016-02-09      6
21                      2016-02-10      7
21                      2016-02-11      8
21                      2016-02-12      9
21                      2016-02-13      10
620646                  2018-01-19      1
620646                  2018-01-20      2
620646                  2018-01-21      3
620646                  2018-01-22      4
620646                  2018-01-23      5
620646                  2018-01-24      6
620646                  2018-01-25      7
620646                  2018-01-26      8
620646                  2018-01-27      9
620646                  2018-01-28      10
639701                  2018-08-30      1
639701                  2018-08-31      2
639701                  2018-09-01      3
639701                  2018-09-02      4
639701                  2018-09-03      5
639701                  2018-09-04      6
639701                  2018-09-05      7
639701                  2018-09-06      8
639701                  2018-09-07      9
639701                  2018-09-08      10

Answer 2

Here's one attempt, add start date and number of days for wanted period in init as fit:

with init(start_date,num_days) as ( select cast('2010-01-01' as date), 1
                                    union all
                                    select cast('2017-11-12' as date), 4)
   , iter(start_date, num_days, dt) as (
       select start_date, num_days, start_date as dt from init
       union all
       select start_date, num_days, dateadd(DAY, 1, dt) 
       from iter where dt < dateadd(DAY, num_days-1, start_date)
)
select start_date,num_days from iter
order by start_date, dt;

Answer 3

Instead of using recursive CTE to produce final date range I would use a different approach. CTE is limited to certain recursion limit. That means it will not generate date ranges longer than this limit (I think by default it is set to 100). In this case, it means you won't be able to create data ranges longer than 100 days.

My proposal is to use CTE to generate the so-called Tally Table and then use it with CROSS APPLY to get the desired result. That will enable you to create any amount of "unlimited" data ranges.

Let's create a table-valued function that will produce for us a range of integer numbers:

CREATE FUNCTION dbo.fnt_Numbers(@startValue int, @amount int)
RETURNS TABLE AS
RETURN 
WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num FROM lv4)
SELECT @startValue + num - 1 AS n
FROM Numbers
WHERE num <= @amount;

You can use it in the following way:

SELECT n FROM dbo.fnt_Numbers(0, 10);

Example result:

Then you can use this code to generate the time ranges you need:

CREATE TABLE #Date (
StartDate date NOT NULL,
DaysAmount int NOT NULL 
);

--Example data
INSERT INTO #Date (StartDate,DaysAmount)
VALUES ('2018-08-31', 10), ('2000-10-10', 5);

--Review table content
SELECT StartDate, DaysAmount 
FROM #Date;

--Generate ranges
SELECT Dates.*
FROM #Date
CROSS APPLY (SELECT DATEADD(day,n, StartDate) AS [Date] , n FROM fnt_Numbers(0, DaysAmount)) AS Dates
ORDER BY StartDate, n;

Results: