Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cumulative Stored Procedure

Posted on 2009-05-15
8
Medium Priority
?
334 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:softdimensions
  • 4
  • 4
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24400107
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
 

Author Comment

by:softdimensions
ID: 24400141
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24400142
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:softdimensions
ID: 24400172
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 24400221
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
 

Author Closing Comment

by:softdimensions
ID: 31582125
Works Great...is this style of query documented anywhere? or is it one of those undocumented features of SQL?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24400352
this is called  co-related subqueries
0
 

Author Comment

by:softdimensions
ID: 24435748
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question