October 16, 2013 at 1:17 am
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
October 16, 2013 at 1:25 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy