Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wallyk
Starting Member
8 Posts |
 Posted - 2006-06-20 : 16:11:06
|
| I am doing some calculations with a table and would like to know how to reference a previous row without having to alter the dates in a query at the start of each month. For example I have to find a percentage change for a series of values. The table has three columns, is based on end of month dates, and the "return" column is the calculated column where the value is. In the example the return value for date 2006-04-30 is Index of (2006-04-30/ Index 2006-03-31)-1.Date Index return2006-03-31 6535 .051302006-04-30 6949.29 .06333 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-06-20 : 17:53:53
|
You can do it using sub-queries. Something like this:SELECT A.Date, A.Index, (SELECT TOP 1 Index FROM myTable WHERE Date < A.Date ORDER BY Date DESC) as Last IndexFROM myTable A This doesn't do the calculation for you, but the basic structure should get you started. HTH,Tim |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-06-20 : 22:43:52
|
| You need to be able to derrive the PK for the 'previous row' using only data in the 'current row'. If you can describe that then the SQL becomes a correlated sub-query or join on it.Alternatively if you use SQL Server 2005 you might be able to do a sub query using row_number but doing that implies you can't adequately describe your data. Timmy's query looks about right although I hate using TOP 1 because it usually implies insuffiecent knowledge of data. If it works without returning a random row (i.e. date is not unique) it should really be a MAX. Is there only one value per date? |
|
|
|
|
|
|
reference previous rows