Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate a running total?

Author  Topic 

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-01-14 : 05:41:40
Hi,

I have 320,000 records in a temporary table that is created each time the query is executed.

There are five fields: value, acctno, yrmonth, division and centre

The table holds data from 2006 and I'd like to be able to calculate a running sum for each acctno based on value. When the acctno changes, the running total should reset to zero and start summing again.

Any hints would be greatly appreciated.

Many thanks, as always.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-14 : 05:45:13
see scenario 1

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-01-14 : 06:07:56
Many thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-14 : 06:27:45
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-14 : 23:26:55
quote:
Originally posted by visakh16

see scenario 1

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Oh, be careful, now. That code contains a "Triangular Join" and, depending on the data, can be hundreds, thousands, and even millions of times worse than a cursor, while loop, or recursive CTE. Please see the following article for more on "Triangular Joins".

[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page
   

- Advertisement -