Solved

Cumulative Stored Procedure

Posted on 2009-05-15
8
325 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now