Recursion Help - CTE

• Ulysses_Infinity

  SSCrazy

  Points: 2588

  More actions

  October 16, 2013 at 1:17 am

  #278084

  Hi, I need help on how to calculate a moving index, based on values in another column. Here's my data:

  CREATE TABLE #temp_RecursionHelp2(

  MonthNumber int NULL,

  FInalMOM float NULL,

  IndexedPerc float NULL

  )

  GO

  insert into #temp_RecursionHelp2

  values

  (1, 1.30613547,100),

  (2, 1.30613547,NULL),

  (3, 1.30613547,NULL),

  (4, -0.2058964,NULL),

  (5, -0.2058964,NULL),

  (6, -0.2058964,NULL),

  (7, -0.70990703,NULL),

  (8, -1.21391765,NULL),

  (9, 0.80212484,NULL),

  (10, -0.2058964,NULL),

  (11, -1.2139176,NULL),

  (12, -0.70990703,NULL);

  What I want to do is recursively update IndexedPerc using the IndexedPerc value from the previous month. So, for example, IndexedPerc for MonthNumber 2 should be:

  MonthNumber 1's IndexedPerc + (MonthNumber 2's FinalMON value + 100)/100

  or:

  100 + (1.30613547 + 100)/100

  And then, Month 3's Indexed Perc value is calculated from this:

  MonthNumber 2's IndexedPerc + (MonthNumber 3's FinalMON value + 100)/100

  etc.

  I can do it with a loop but have a feeling a recursive CTE may help, but can't get the syntax right. Any help greatly appreciated.

  Thanks - Jaosn

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  October 16, 2013 at 1:25 am

  #1658804

  This seems to be some sort of running total.

  You can solve this without a cursor and without a recursive CTE.

  Read the following (long and advanced) article by Jeff Moden on running totals:

  Solving the Running Total and Ordinal Rank Problems[/url]

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP