Cumulative Stored Procedure

see the data below.  I need a stored proc that takes in a start month and year and an end month and year and returns a result set that will give me cumulative totals.  I.E.  row 1 will be the sum of the value column for the start month, row 2 will be the sum of the value of Start Month and Start Month + 1, row 3 will be the sum of the value for Start Month, Start Month + 1, Start Month + 2


So the result set would look like the following
2008    1     1.02
2008    2     3.04
2008    3     6.06
etc

What is the best way to approach this

Thanks
Year      Month  Value
2008	1	1.02
2008	2	2.02
2008	3	3.02
2008	4	4.02
2008	5	5.02
2008	6	6.02
2008	7	7.02
2008	8	8.02
2008	9	9.02
2008	10	10.02
2008	11	11.02
2008	12	12.02

Open in new window

softdimensionsAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT a.Year, a.month ,
a.value+ ISNULL( (SELECT SUM(b.Value) from urTable b where (b.Year = a.Year and b.month < a.month) or b.Year < a.year ),0 )
FROM urTable a
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT a.Year, a.month, a.value+ISNULL(b.Value, 0)
FROM urTable a
LEFT JOIN urTable b on a.Year = b.Year and b.month < a.month
0
 
softdimensionsAuthor Commented:
Your SQL returned more than 1 row per month so I modified it to the following

SELECT a.OpData_Year, a.OpData_month, sum(a.OpData_value)+ISNULL(sum(b.OpData_Value), 0)
FROM OpData_Monthly a
LEFT JOIN OpData_Monthly b on a.OpData_Year = b.OpData_Year and b.OpData_month < a.OpData_month
Group by a.OpData_year,a.OpData_Month
Having a.OpData_Month <4

Now using the above the results are as follows

2008      1      1.02
2008      2      3.04
2008      3      9.08

The first 2 are correct but the value for row 3 should be 6.06

Thanks
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
sorry try this

SELECT a.Year, a.month , a.value+ ISNULL( (SELECT SUM(b.Value) from urTable b where b.Year= a.Year and b.month < a.month ),0 )
FROM urTable a
0
 
softdimensionsAuthor Commented:
That works great when only using 1 Year such as 2008 but it resets when the data goes into 2009.  How do I modify the script so that 2009 month 1 will be seen as greater than Dec 2008, etc
0
 
softdimensionsAuthor Commented:
Works Great...is this style of query documented anywhere? or is it one of those undocumented features of SQL?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
this is called  co-related subqueries
0
 
softdimensionsAuthor Commented:
another question that relates is
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24425788.html

please lend your expertise to that question if you can.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.