I am struggling on a query problem but it might be a piece of cake to you. Please help. I have a table with three columns: Year | Month | Amount. I would like my query to generate a result set with each row amount becomes accumulative which accumulate over years. For example: 2005 | 11 | $400 2005 | 12 | $100 2006 | 1 | $200 The result set should be: 2005 | 11 | $400 2005 | 12 | $500 2006 | 1 | $700 Thanks alot
1 If you use Reports, you should its Running Total Feature 2 Select Year, Month, (Select sum(amount) from table where Year<=T.year) as Amount from table 3 Do Google search on Running total +sql server. You will find lot of links 4 Cursor seems faster in this case. Do article search here on mmarovic also you may need to read Adam Mechanic's article Madhivanan Failing to plan is Planning to fail
The link to my article is:http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp There are couple of techniques how to do it in t-sql, but it is better to do it on the client side.
Also read http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13574 Madhivanan Failing to plan is Planning to fail
I missed it is in reporting services forum, in this case Madhivanan recommendation #1 is all you needed to know.
