May 4, 2014 at 7:10 pm
About six months ago, one of the regular SSC forum experts gave me this query to use to build a Time lookup table. It works great and I use it to join it to another table to create space holders for any minute of the day for which the other table doesn't have any values.
I'd like to understand the syntax of this borrowed query, not because anyone is asking me to explain it as the Time table exists and I can rebuild it anytime, but because it bothers me that I don't understand something I borrowed (I think from dwain) to accomplish a task:
;WITH e1(n) AS(
SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)
),
e2(n) AS(
SELECT e1.n FROM e1, e1 x
),
e4(n) AS(
SELECT e2.n FROM e2, e2 x
),
e8(n) AS(
SELECT e4.n FROM e4, e4 x
),
cteTally(n) AS(
SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM e8
),
Test(n) AS(
SELECT DATEADD( minute, n, DATEADD( YEAR, -2, GETDATE()))
FROM cteTally)
select DATEADD(mi,datediff(mi,0,n),0)as cte_start_date
INTO Sandbox.dbo.Time
FROM Test
WHERE n <= DATEADD( YEAR, 4, GETDATE())
I have two questions before I think I will understand:
(1) how do 'e1 x', 'e2 x', and 'e4 x' accomplish the task of ballooning the dataset in respective prior ctes by exponent of 10? This syntax is totally unfamiliar to me.
(2) what is 'ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1' accomplishing?
May 4, 2014 at 7:46 pm
KoldCoffee (5/4/2014)
I'd like to understand the syntax of this borrowed query, not because anyone is asking me to explain it as the Time table exists and I can rebuild it anytime, but because it bothers me that I don't understand something I borrowed (I think from dwain) to accomplish a task:
I wish there were more people like you. I'm working on your good questions now.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2014 at 8:12 pm
ok, looking forward to it and thanks. I see it is not so easy.
May 4, 2014 at 9:15 pm
Here is a quick explanation using comments. Expect a much more detailed explanation from Jeff, but hopefully this helps get you closer to understanding what it does and how. Also, I did make a few minor changes.
One question up front, is this supposed to generate 6 years of values?
WITH
e1(n) AS (SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)), -- generate a dynamic table with 10 rows
e2(n) AS (SELECT a.n FROM e1 a, e1 x), -- cross join e1 with itself to generate a dynamic table with 100 rows
e4(n) AS (SELECT a.n FROM e2 a, e2 x), -- cross join e2 with itself to generate a dynamic table with 10,000 rows
e8(n) AS (SELECT a.n FROM e4 a, e4 x), -- cross join e4 with itself to generate a dynamic table with 100,000,000 rows
cteTally(n) AS (SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM e8),
-- select the first 6,307,204 rows of e8 and number them sequentially starting with 0 (the -1) to 6307203 (6307204 - 1)
Test(n) AS (SELECT DATEADD( minute, n, DATEADD( YEAR, -2, GETDATE())) FROM cteTally)
-- Starting with the current date and time minus 2 years, add n minutes to the datetime
select
DATEADD(mi,datediff(mi,0,n),0)as cte_start_date -- drop (or zero) the seconds from the date time value
INTO
Sandbox.dbo.Time
FROM
Test
WHERE
n <= DATEADD( YEAR, 4, GETDATE()) -- don't think this works as expected, see question above.
May 4, 2014 at 9:25 pm
KoldCoffee (5/4/2014)
(2) what is 'ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1' accomplishing?
Leaving the explanation of the Cartesian Product to Jeff, here is a quick answer to the second question:
The ROW_NUMBER function generates a dense numerical sequence starting at 1. The " - 1" simply shifts it to start at 0 (zero).
It requires an ORDER BY within the OVER clause, "(SELECT NULL)" tells the server to ignore any order or rather, not to sort it.
😎
May 4, 2014 at 9:59 pm
Lynn Pettis (5/4/2014)
Expect a much more detailed explanation from Jeff...
You know me all too well, ol' friend. 😀
Ok... here we go, KoldCoffee...
No insult intended… I'm going to start at the very bottom because I don't know what you do or do not know and I want to make sure that you understand, especially since you asked.
First, what does this do?
SELECT 1
UNION ALL
SELECT 1
;
That's right. It returns two rows with a "1" on each row.
-----------
1
1
(2 row(s) affected)
The reason why it returned 2 rows instead of just one unique "1" is because a UNION ALL was used instead of just a UNION which has an implicit "distinct" I it.
The problem is, there's no title on the column for us to refer to the data by. Let's fix that right now. We could add the column name in all sorts of ugly ways but let's treat the query as if it were a table by adding it to the FROM clause of a new SELECT, giving it an alias as if it were a table, and then externally declare the column name. Like this…
SELECT d.N
FROM (
SELECT 1
UNION ALL
SELECT 1
) d (N)
;
See the "d (N)" thingy? The "d" is an alias for the result set that we're treating as if it were a table and the "(N) defines what we want to call the column. Here's the result…
N
-----------
1
1
Now, fully functional queries that return their own result sets are called "derived tables" (that's why I used the "d" alias here… anything could have been used but "d" reminds me that it's a "derived table" for this demo) or "inline views". What's really cool as of 2005 is that we can move such things into a "top down" ordering to make them more readable, a little easier to use, and a bit more flexible for other things. We can move them into a structure known as a "Common Table Expression" or "CTE", which is also classified as a "derived table" and "inline view". We basically just have to move the query from the FROM clause to the CTE and then reference the CTE from the outer SELECT. Like this…
WITH
d (N) AS
(
SELECT 1
UNION ALL
SELECT 1
)
SELECT N
FROM d
;
The next thing to do is to add more rows. Let's start with just 10. We'll also use those rows in the outer query to start counting using ROW_NUMBER(). Like this…
WITH
d (N) AS
( --=== This returns 10 rows of all 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
)
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
FROM d
;
Here's the result…
N
--------------------
0
1
2
3
4
5
6
7
8
9
A couple of things to notice here. Although ROW_NUMBER() requires an ORDER BY clause, we don't actually care what the ORDER BY is actually based on especially since all the rows from the "d" CTE have a "1" in them. So it's customary (and sometimes faster) to order by a constant. ROW_NUMBER() doesn't allow a constant in the ORDER BY so we trick it by embedding a SELECT statement that has the constant of NULL.
ROW_NUMBER() always starts at the number "1". If we want this sequence of 10 numbers to start at "0" instead of "1", one way to pull it off is to subtract 1 from every number that ROW_NUMBER() makes. That's what the purpose of the "-1" is (although I'd have done it in a different manner).
As a bit of a side bar, the 10 SELECT UNION ALLs can be replaced with a single VALUES function like that found in the code you posted on SQL SERVER 2008 and above. It doesn't make things any faster but it does cut down on the clutter in the query.
Time for another question.. What does this do?
SELECT 1
FROM (SELECT 1 UNION ALL SELECT 1) d1 (N)
CROSS JOIN (SELECT 1 UNION ALL SELECT 1) d2 (N)
;
If we look at the code, it has two "derived tables" in it and each "derived table" has two rows in it. We're doing a CROSS JOIN (also known as a "Cartesian Product") between the two "derived tables". That means for every row in one of the "derived tables", we'll return all of the rows of the other "derived table". Think of it as us multiplying the number of rows in the "derived tables" to come up with a "Product" or answer to the multiplication problem.
Since there are 2 rows in each of the "derived tables", our result set will contain 2*2 or 4 rows of "1's". Like this…
-----------
1
1
1
1
If we had 10 rows in each "derived table", we would have ended up with 10*10 or 100 rows each containing a "1".
Note the we can use the NON-ANSI version of a CROSS JOIN, which is nothing more than listing the 2 derived tables in the FROM clause separated only by a comma and no criteria. Like this…
SELECT 1
FROM (SELECT 1 UNION ALL SELECT 1) d1 (N),
(SELECT 1 UNION ALL SELECT 1) d2 (N)
;
Shifting gears a bit, the "engineering notation" for "10" is 1*10^1. The "engineering notation" for "100" is 1*10^2. The exponent tells how many times "10" has been multiplied by itself.
Let's apply all of that to our CTE so we can count to 100 using CTEs.
WITH
E1(N) AS
( --=== This returns 10 rows of all 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b) --CROSS JOIN gives us 10*10 or 100 rows
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
FROM E2
;
Here, we change the name of the first CTE to represent the power of 10 that it produces. The shorthand for "engineering notation" for 1*10^1 is 1E1. We just dropped the leading 1 because it's superfluous for us.
The neat thing about CTEs is that, unlike "derived tables" in a FROM clause, we can refer to them in other "derived tables". So, our second CTE (E2), creates another "derived table" by doing a CROSS JOIN on the first (E1) "derived table" (CTE) against itself and that will return 100 rows. The "a" and "b" are just different aliases that don't mean anything but are necessary or SQL Server will complain about referencing the same "derived table" more than once.
Another name for one CTE referring to another CTE is called "Cascading CTEs". You probably won't find that particular name in a book anywhere. We made up that name right here on SSC.
The final SELECT was modified to take its input from the E2 CTE and returns the values of 0 to 99.
What if we only wanted to count to 53 and didn't want to change the code for either the E1 or E2 CTEs? That's where the TOP function comes in. Like this…
WITH
E1(N) AS
( --=== This returns 10 rows of all 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b) --Cross join gives us 10*10 or 100 rows
SELECT TOP (53) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
FROM E2
;
Which 53 numbers will it return? The ORDER BY of the ROW_NUMBER() takes care of that. It'll return the numbers 1 through 53. Since we're subtracting 1 from each number, the final result set will be 0 through 52, in this case. We could use a variable instead of a hard-coded "53" to make this cascading, counting CTE even more useful.
Now… what if we wanted a really big number like the number 6307204 that was used in the original code you posted? The answer is that we just need more CROSS JOINs in the form of additional cascading CTEs. The really cool part is that if we multiply the number of rows in E2 (which is 100 rows) times itself, we don't just multiply by 10. No, we're multiplying by 100 now. A CROSS JOIN on E2 will give use E4 or 100*100 which is the same as 1*10^4 or 10000. Why E4? If you add exponents (E2+E2), you're actually multiplying. E2 is 100 with 2 zeros after a 1. 10000 is a 1 with 4 zeros after it. We just doubled the number of zeros.
Once we have E4, it's simple to see how to get to E8, which will return E4*E4 or 10000*10000 or a 1 with 8 zeros after it. That’s 100,000,000 or a 100 MILLION!
Here's the code for that. Looks almost identical to the original code.
WITH
E1(N) AS
( --=== This returns 10 rows of all 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --CROSS JOIN gives us 10*10 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --CROSS JOIN gives us 100*100 or 10000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b) --CROSS JOIN gives us 10000*10000 or 100000000 rows
SELECT TOP (53) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
FROM E8
;
Notice that each new CTE refers to the previous CTE in the FROM clause and that the final SELECT refers to the last CTE.
What the ROW_NUMBER() thing is doing is that it is using the "presence of rows" from the last CTE as a "cursor" or "loop". It's not even using the "1's" from the CTE's, just the "presence of rows". This is what R. Barry Young refers to as a "pseudo cursor". It uses the very, very fast machine language "loops" that SQL Server does behind the scenes instead of an explicit loop. ROW_NUMBER() simply counts by "1's" for each row that it encounters.
Like I said, there are some optimizations that could be made here. For one (no pun intended) we can UNION ALL an explicit "0" to make counting faster by not having to subtract 1 for each count. For larger numbers, this can add up to quite a time savings.
Another performance improvement is to limit the number of "nested loops" behind the scenes. We can do that simply by doing more CROSS JOINs in the E2 CTE and cascading that effect "down". Notice that the E2 CTE has been updated to 4 CROSS JOINs and renamed to be E4.
Like this…
WITH
E1(N) AS
( --=== This returns 10 rows of all 1's
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
),
E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), -- 10*10*10*10 or 10000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 10000*10000 or 100000000 rows
SELECT 0 UNION ALL --Removes the need to subtract 1 from every count
SELECT TOP (53-1) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E8
;
Last but certainly not least, the cascading CTEs are "cascading" in appearance only. They all become part of the overall query. What that means is that they don't count up to 100 Million (in this case) and then apply the TOP (). The TOP is expressed across ALL of the CTE's until it's satisfied as to the number of rows. If the TOP() was only "3", then only the first 3 SELECT UNION ALLs would execute. E4 and E8 would be present but they would do "0" rows.
Lemme know if you have any other questions about this wonderful tool first created by Itzik Ben-Gan and improved over time not only by Itizik, but by others on this forum.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2014 at 9:03 am
While it won't change the functionality of anything consider altering
SELECT e1.n FROM e1, e1 x
to
SELECT e1.n
FROM e1
CROSS JOIN e1 x
As this makes it more clear a Cartesian join is requested.
May 5, 2014 at 9:45 am
OK folks, just so you know, I've been reading and re-reading these replies, especially Jeff Moden's to make sure everything is completely clear to me. Then, after that if I still have questions I just can't figure out I'll post. It's really wonderful you are here helping me.
At this point, the recurrent question lurking in my mind is 'why do we have to count from zero' instead of '1' referring to 'SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
'...but sometimes these things become clear after the 5th reading etc. (think tortoise and the hare)
May 5, 2014 at 10:56 am
the starting at zero vs starting at one is because the function is adding time elements to generate your matrix of data
SELECT DATEADD( minute, n, DATEADD( YEAR, -2, GETDATE()))
if it started at 1, your starting time would be 12:01 instead of 12:00.
you could still use a 1-based tally table to generate the desired data, but then you have to make the calculation take that extra, initial minute into consideration;
SELECT DATEADD( minute, n -1, DATEADD( YEAR, -2, GETDATE()))
so in situations where you are fiddling with dates, it's prettier to manipulate the Tally/numbers you generate, instead of the functions using the generated values.
Lowell
May 5, 2014 at 3:23 pm
I suggest making your code as self-documenting as possible; then you need fewer additional comments. Naturally that means avoiding variable "names" like "E", "N" and so on. Example for this code below.
Of course you may prefer to adjust the CROSS JOINs for efficiency and/or adjust the names, but make the names meaningful. Because tally tables are quite common, I keep the first two names the same, but the final use of the tally table should have a column name that identifies specifically what the data is.
;WITH
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT
[1000000s].digit * 1000000 + [100000s].digit * 100000 + [10000s].digit * 10000 +
[1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS minutes_increment
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
CROSS JOIN cteDigits [100s]
CROSS JOIN cteDigits [1000s]
CROSS JOIN cteDigits [10000s]
CROSS JOIN cteDigits [100000s]
CROSS JOIN cteDigits [1000000s]
WHERE
[1000000s].digit * 1000000 + [100000s].digit * 100000 + [10000s].digit * 10000 +
[1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit
--limit rows generated to 6 yrs' worth of minutes
<= DATEDIFF(MINUTE, '19000101', '19060101')
),
cteTime_by_minute AS (
SELECT DATEADD(MINUTE, minutes_increment, DATEADD(YEAR, -2, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()), 0)))
AS time_by_minute
FROM cteTally t
)
SELECT time_by_minute
INTO Sandbox.dbo.Time
FROM cteTime_by_minute
Edit: Added "INTO Sandbox.dbo.Time".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 5, 2014 at 5:55 pm
KoldCoffee (5/5/2014)
At this point, the recurrent question lurking in my mind is 'why do we have to count from zero' instead of '1' referring to 'SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
It's just simple math. Since we want to use all the values that ROW_NUMBER() produces by adding them to the start date/time to come up with a date/time incremented by minute, the first value has to be "0" so that when you add the value of ROW_NUMBER() to the start date/time, the first value (the one with the "0") will return the start date/time as expected. Since the first value (1) was shifted towards "0" by 1, all the values must be shifted towards "0" by 1 or you'll end up with a gap. Subtracting 1 effectively moves the value that ROW_NUMBER() produced towards "0" by 1.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2014 at 7:41 pm
OK, what a day of goodness from remote corners
I most of all appreciate Jeff Moden’s top to bottom explanation of the query, but all posts deepened the understanding of many concepts and creativity that goes into clever SQL.
I get the asci and non asci standard for cross apply notation (which I didn’t before, particularly because I was tripped up with the ‘x’ thinking it was a multiplication symbol instead of an alias), the UNION ALL replacing the VALUES syntax (whose x n I didn’t and still don’t understand but don't have to), replacing derived queries with CTEs, CTE syntax/structure, explaining 1 as placeholder so you have something to assign to row_numbers, harnessing Row_Number to get rows numbered by using the ORDER BY NULLs trick, appreciate the performance observations...but….why the row numbers have to start as ZERO still eludes me…..even if Lowell gave an explanation that smacked of something that I should get, and even though ‘ it is simple math’. I thought I knew simple math. I now see how it works (ie. I tested it with and without zero, at least I can see the purpose), but not why.
The - 1 is more about logic than math....and I don't expect you to help me there actually.
So, you people are pretty wonderful. Thanks.
May 5, 2014 at 9:13 pm
KoldCoffee (5/5/2014)
but….why the row numbers have to start as ZERO still eludes me…..
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2014 at 12:32 am
yes I see now.
The 'n' in the second to last CTE of the original query that I posted called Test(n) refers to the row_number values assigned to n column in the cte prior to that, and is also used by the DATEADD function to control the number of minutes to increment by.
so, if it is important that the first datetime stamp in the first row not increment at all, than the row_number in the first row needs to =0.
I figure at this rate of chugging through such problems, I will, at the age of 50, be a plausible thinker:Wow: To be the one to actually come up with these solutions would be awesome.
May 6, 2014 at 10:38 am
I ran out of time yesterday and will try to get to it tonight but some of the "solutions" for the original problem have some major flaws in them even though they produce the correct answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply