Solved

Cumulative Stored Procedure

Posted on 2009-05-15
8
329 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

829 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