No problem. Let's start over there.

Let's make the problem much smaller. Let's say we have a start date/time of 1900-01-01 00:00 and we want to create just 4 times, [font="Arial Black"]starting at the start date/time[/font], that have an increment of just 1 minute.

In the following, the CTE takes the place of ROW_NUMBER() just for this example. We want 4 date/times and ROW_NUMBER() always starts at 1, so the CTE has the values of 1, 2, 3, and 4 in it. Let's use those numbers as if they were minutes and add them to the start date/time and see what happens. Here's the code...

DECLARE @StartDT DATETIME;

SELECT @StartDT = '1900-01-01 00:00';

WITH

cte(RowNumber) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)

SELECT StartDate = @StartDT

,RowNumber

,[@StartDT+RowNumberAsMinutesAdded] = DATEADD(mi,RowNumber,@StartDT)

FROM cte

;

And here are the results of that code...

StartDate RowNumber @StartDT+RowNumberAsMinutesAdded

----------------------- ----------- --------------------------------

1900-01-01 00:00:00.000 1 [highlight]1900-01-01 00:01:00.000[/highlight]

1900-01-01 00:00:00.000 2 1900-01-01 00:02:00.000

1900-01-01 00:00:00.000 3 1900-01-01 00:03:00.000

1900-01-01 00:00:00.000 4 1900-01-01 00:04:00.000

(4 row(s) affected)

That looks fine. All of the date/times are exactly one minute apart. The only problem is, they all off by 1. We wanted the first date/time (the one that's highlighted) to be the same as the start date/time.

What do we have to do to fix that?

.

.

.

.

.

Subtract 1 from the row number. 🙂 Like this...

DECLARE @StartDT DATETIME;

SELECT @StartDT = '1900-01-01 00:00';

WITH

cte(RowNumber) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)

SELECT StartDate = @StartDT

,RowNumber

,[@StartDT+RowNumberAsMinutesAdded] = DATEADD(mi,RowNumber,@StartDT)

,[RowNumber-1] = RowNumber-1

,[@StartDT+RowNumber-1AsMinutesAdded] = DATEADD(mi,RowNumber-1,@StartDT)

FROM cte

;

And now we the first time (highlighted) starts at the start date/time just like we wanted. All of the rest of the dates also fell in line because we subtracted 1 from all of row numbers.

StartDate RowNumber @StartDT+RowNumberAsMinutesAdded RowNumber-1 @StartDT+RowNumber-1AsMinutesAdded

----------------------- --------- -------------------------------- ----------- ----------------------------------

1900-01-01 00:00:00.000 1 1900-01-01 00:01:00.000 0 [highlight]1900-01-01 00:00:00.000[/highlight]

1900-01-01 00:00:00.000 2 1900-01-01 00:02:00.000 1 1900-01-01 00:01:00.000

1900-01-01 00:00:00.000 3 1900-01-01 00:03:00.000 2 1900-01-01 00:02:00.000

1900-01-01 00:00:00.000 4 1900-01-01 00:04:00.000 3 1900-01-01 00:03:00.000

(4 row(s) affected)

The bottom line is that we're using ROW_NUMBER() as the number of minutes that we want to add to the start date/time. If we want the first date/time returned to be the start date/time, then we need to add 0 to it. Since ROW_NUMBER() always starts at 1, we have to subtract 1 from it to get the 0. Everything else just falls in line.