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 |
|
Gymratz
Starting Member
2 Posts |
 Posted - 2013-11-21 : 14:29:38
|
| Based on variables I will be pulling a query of 12-120 rows that has two columns, date & return.I'd like to add a new column that is the amount.My starting value would be 1, and each row would need to be the result of the row above it times by the return.Thus if the first month had a 1% return it would have 1.01 as the new amount.If the next month had a 5% return it would have a value of 1.0605.I'm not sure how to write this recursive query where Column C always references (previous Column C) * Column B. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-21 : 14:46:45
|
Yes, you could use cte like shown below. If your dates are not consecutive (i.e., if you don't have returns on weekend dates, for example) then you would first need to add a row number (which can be done in another cte) for this to work.DECLARE @StartDate DATE = '20130101';;WITH cte AS( SELECT [Date],[Return],1.0*[Return] AS CumumlativeReturn FROM YourTable WHERE [Date] = @StartDate UNION ALL SELECT y.[Date],y.[Return], c.CumulativeReturn*(1.0+y.[Return]) FROM cte c INNER JOIN YourTable y ON y.Date = DATEADD(dd,1,c.Date))SELECT * FROM cte OPTION (MAXRECURSION 0); |
 |
|
|
Gymratz
Starting Member
2 Posts |
Posted - 2013-11-21 : 15:19:01
|
| Thank you, that appears to have worked quite well!My second biggest problem was that my dates are always month end dates... I feel like the way I wrote that portion may not be the best; however, what I did was added 1 day, added 1 month, subtracted 1 day.WBCD2.Month_End_Date = DATEADD(d, -1,DATEADD(m, 1, DATEADD(d, 1, c.Month_End_Date)))I got the results I needed! Thanks! |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-21 : 17:24:49
|
| The way you are doing month end calculations is correct; many be there are opportunities to simplify it. If you were on SQL 2012, there is EOMONTH function which would make it a little simpler. |
|
|
|
|
|
|
|
|
Growth of a Dollar (Recursive Query?)