February 13, 2009 at 9:22 am
I have a Table called tblcountspeed. It contains 2 columns of random numbers.
I want to do a select query that adds a 3rd column that performs mathematical calcs on the existing row and the one above it. Below is the actual data and the type of query results I am looking for.
tblcountspeed
Col1 Col2
10 50
20 200
30 75
45 150
Query Results Desired
Col1 Col2 QueryColumn
10 50 (null or error. Either is acceptable for this first row)
20 200 15
30 75 -12.5
45 150 5
Here is this same data in an MS Excel sheet so you can see the calculations
A B C
Col1 Col2 QueryColumn
1 10 50 (null or error. Either is acceptable for this first row)
2 20 200 =SUM(B2-B1)/SUM(A2-A1)
3 30 75 =SUM(B3-B2)/SUM(A3-A2)
4 45 150 =SUM(B4-B3)/SUM(A4-A3)
Is this possible in a query?
February 13, 2009 at 9:28 am
It's possible, but there are caveats on it. Take a look for Jeff Moden's Running Totals article on this site.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 13, 2009 at 11:16 am
Thanks for the tip.
I assume you are referring to http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Unfortunately, this only has a SQL script download and that script does not seem to apply to my situation. Perhaps if I weren't so green at all this I could make more use of it.
I guess i'm looking for a more "plain english" explanation of this process to go along with the code.
February 13, 2009 at 11:50 am
;WITH cteCountSpeed AS
(
SELECTROW_NUMBER() OVER ( ORDER BY Col1 ) AS Row, *
FROMtblCountSpeed
)
SELECTC1.Col1, C1.col2, ( C2.Col2 - C1.Col2 ) / ( C2.Col1 - C1.Col1 ) AS QueryColumn
FROMcteCountSpeed C1
LEFT OUTER JOIN cteCountSpeed C2 ON C1.Row = C2.Row - 1
Check if this works or if it gives you some idea..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 13, 2009 at 11:59 am
kingstonnadar (2/13/2009)
;WITH cteCountSpeed AS(
SELECTROW_NUMBER() OVER ( ORDER BY Col1 ) AS Row, *
FROMtblCountSpeed
)
SELECTC1.Col1, C1.col2, ( C2.Col2 - C1.Col2 ) / ( C2.Col1 - C1.Col1 ) AS QueryColumn
FROMcteCountSpeed C1
LEFT OUTER JOIN cteCountSpeed C2 ON C1.Row = C2.Row - 1
Check if this works or if it gives you some idea..
The CTE may not work. This is a SQL Server 7/SQL Server 2000 forum.
February 13, 2009 at 12:20 pm
You are correct Lynn.
CTE is SQL2005 only and my server is a 2000
February 14, 2009 at 9:59 am
Sorry for the error. I actually missed see the forum name. I think we can use the table variables or temporary tables with an identity column to solve this problem.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 16, 2009 at 7:26 am
I got my hands on a 2005 server to test on and the cte works well. The only change I made was that I changed the last entry to +1 to get the results on the desired row.
Unfortunately, my production server is still 2000 and a upgrade is not on the horizon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply