0

Possible Duplicate:
Calculate a Running Total in SqlServer

I'm trying to calculate the cummulative return without using a cursor. I'm using SQL Server 2005.

The calculation in the cursor is as follows: 

SET @CumRtn = (@CumRtn * (1 + @PeriodRtn))

Here is the data with the correctly calculated Cummulative Return. 

0       A           B               C               D
1   ValueDate   PeriodReturn    CummulativeRtn  Excel Formula
2   31-May-96   100             100             100 
3   30-Jun-96   0.00672617      100.672617      =D2 * (1+B3) 
4   31-Jul-96   -0.0473345      95.90732905     =D3 * (1+B4) 
5   31-Aug-96   0.014525272     97.30040907     =D4 * (1+B5) 
6   30-Sep-96   0.049063368     102.0742949     =D5 * (1+B6) 
7   31-Oct-96   0.005499141     102.6356158     =D6 * (1+B7) 
8   30-Nov-96   0.055675119     108.3498659     =D7 * (1+B8)

So far I haven't had much luck reproducing the numbers above, any ideas?

Let me know if I can provide any further data.

Thanks, Patrick

Community
  • 1
  • 1
user1089999
  • 1
  • How large is your dataset? cursors may well be the best way if more than a few hundred rows. – Martin Smith Dec 09 '11 at 16:06
  • See my answer [to this SO post](http://stackoverflow.com/questions/8448581/recursive-common-table-expression) which sums up values with a recursive CTE - could that be of use to you?? – marc_s Dec 09 '11 at 16:50
  • [Or my answer here shows how to use a Recursive CTE when you haven't got a sequential column to join on](http://stackoverflow.com/a/7454564/73226) – Martin Smith Dec 09 '11 at 18:39

0 Answers0