Cannot increment values in a T-SQL CTE

Question

I have a case where I need to write a CTE ( at least this seems like the best approach) . I have almost everything I need in place but one last issue. I am using a CTE to generate many millions of a records and then I will insert them into a table. The data itself is almost irrelevant except for three columns. 2 date time columns and one character column.

The idea behind the CTE is this. I want one datetime field called Start and one int field called DataValue. I will have a variable which is the count of records I want to aim for and then another variable which is the number of times I want to repeat the datetime value. I don't think I need to explain the software this data represents but basically I need to have 16 rows where the Start value is the same and then after the 16th run I want to then add 15 minutes and then repeat. Effectively there will be events in 15 minute intervals and I will need X number of rows per 15 minute interval to represent those events.

This is my code

Declare @tot as int;
Declare @inter as int;
Set @tot = 26
Set @inter = 3;

WITH mycte(DataValue,start) AS 
(
SELECT 1 DataValue, cast('01/01/2011 00:00:00' as datetime) as start
UNION all
if DataValue % @inter = 0 
    SELECT 
        DataValue + 1, 
        cast(DateAdd(minute,15,start) as datetime)
else
    select 
        DataValue + ,
        start

FROM    mycte   
WHERE   DataValue + 1 <= @tot)


select  
    m.start,
    m.start,
    m.Datavalue%@inter
from mycte as m
option (maxrecursion 0);

I'll change the select statement into an insert statement once I get it working but the m.DataValue%@inter will make it repeat integer when inserting so the only thing I need is to figure out how to make the start be the same 16 times in a row and then increment

It seems that I cannot have an IF statement in the CTE but I am not sure how to accomplish that but what I was going to do was basically say if the DataValue%16 was 0 then increase the value of start.

In the end I should hopefully have something like this where in this case I only repeat it 4 times

+-----------+-------------------+
| DateValue |       start       |
+-----------+-------------------+
|         1 | 01/01/01 00:00:00 |
|         2 | 01/01/01 00:00:00 |
|         3 | 01/01/01 00:00:00 |
|         4 | 01/01/01 00:00:00 |
|         5 | 01/01/01 00:15:00 |
|         6 | 01/01/01 00:15:00 |
|         7 | 01/01/01 00:15:00 |
|         8 | 01/01/01 00:15:00 |

Is there another way to accomplish this without conditional statements?

I guess one option is to use ROW_NUMBER(), and then use div/mod functions to determine when and how to increment things (for instance, the start date would be "start date + ((row_number DIV count_per_intervial) * 15 minutes). And using a number table is probably a better idea than a recursive CTE. See https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table — pmbAustin, Apr 29 '19 at 20:10

Ali Al-Mosawi · Accepted Answer · 2019-04-29T20:36:09.860

You can use case when as below:

Declare @tot as int;
Declare @inter as int;
Set @tot = 26
Set @inter = 3;

WITH mycte(DataValue,start) AS 
(
SELECT 1 DataValue, cast('01/01/2011 00:00:00' as datetime) as start
UNION all

    SELECT DataValue+1 [Datavalue],
        case when (DataValue % @inter) = 0  then cast(DateAdd(minute,15,start) as datetime) else  [start] end [start] 
FROM    mycte   
WHERE   (DataValue + 1) <= @tot)

select  
    m.DataValue,
    m.[start]
from mycte as m
option (maxrecursion 0);

This will give the below result

DataValue    Start
=========    =============
1   2011-01-01 00:00:00.000
2   2011-01-01 00:00:00.000
3   2011-01-01 00:00:00.000
4   2011-01-01 00:15:00.000
5   2011-01-01 00:15:00.000
6   2011-01-01 00:15:00.000
7   2011-01-01 00:30:00.000
8   2011-01-01 00:30:00.000
9   2011-01-01 00:30:00.000
10  2011-01-01 00:45:00.000
11  2011-01-01 00:45:00.000
12  2011-01-01 00:45:00.000
....
26  2011-01-01 02:00:00.000

And if you dont want to use case when you can use double recursive cte as below:-

WITH mycte(DataValue,start) AS 
(   --this recursive cte will generate the same record the number of @inter
    SELECT 1 DataValue, cast('01/01/2011 00:00:00' as datetime) as start
    UNION all
    SELECT DataValue+1 [DataValue],[start] 
    FROM    mycte   
    WHERE   (DataValue + 1) <= @inter)
,Increments as (
    -- this recursive cte will do the 15 additions
    select * from mycte
    union all
    select DataValue+@inter [DataValue]
    ,DateAdd(minute,15,[start])  [start]  
    from Increments
    WHERE   (DataValue + 1) <= @tot
)
select  
    m.DataValue,
    m.[start]
from Increments as m
order by DataValue
option (maxrecursion 0);

it will give the same results.

Thank you. I thought I looked into Case but I guess not. Just learned about CTE's today. Thank you so much. — Chris Jones, Apr 29 '19 at 20:24

score 0 · Answer 2 · answered Apr 29 '19 at 20:12

You can do this with a tally table and some basic math. I'm not sure if your total rows are @tot or should they be @tot * @inter. If so, you just need to change the TOP clause. If you need more rows, you just need to alter the tally table generation.

Declare @tot as int;
Declare @inter as int;
Set @tot = 26
Set @inter = 3;

WITH 
E(n) AS(
    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
    SELECT a.n FROM E a, E b
),
E4(n) AS(
    SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
    SELECT TOP( @tot) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
    FROM E4
)
SELECT n, DATEADD( MI, 15* ((n-1)/@inter), '20110101')
FROM cteTally;

Cannot increment values in a T-SQL CTE

2 Answers2