Adding values in a column - sequentially

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

SQL Server 2005 Forums

Transact-SQL (2005)

Adding values in a column - sequentially

Author

Topic

Lurmis
Starting Member

4 Posts

Posted - 2009-02-11 : 11:59:47

I have a need to add the value of one field to the value of the previous field in the same column, and continue adding each value until a limit is reached.

Example: With a limit of 60

Returned Data would look like this.

RequiredQty | RequiredDate | CumulativeTotal
| |
12 | 2009-01-21 | 12
15 | 2009-01-28 | 27
20 | 2009-02-12 | 47
10 | 2009-02-20 | 57

I need to create and calculate the column 'CumulativeTotal'

Any suggestions?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 12:11:43

In SQL 2000

Select RequiredQty,RequiredDate,
(Select Sum(RequiredQty) from Yourtable Where RequiredQty <= T.RequiredQty)as CumulativeTotal
from yourTable T

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 12:20:20

[code]--Create TV

Declare @T table
(RequiredQty int, RequiredDate datetime)

Insert @T
Select 12,'2009-01-21' union all
Select 15,'2009-01-28' union all
Select 20,'2009-02-12'

--Roll SQL Engine

Select t.RequiredQty,t.RequiredDate,m.CumulativeTotal
from @T t
OUTER APPLY
(select Sum(RequiredQty) as CumulativeTotal
from @T
Where RequiredQty <= t.RequiredQty)m[/code]

Lurmis
Starting Member

4 Posts

Posted - 2009-02-11 : 13:17:15

What does the union all do when inserting the values into @T?
The RequiredQty and RequiredDate are variables

quote:

Originally posted by sodeep

--Create TV

Declare @T table
(RequiredQty int, RequiredDate datetime)

Insert @T
Select 12,'2009-01-21' union all
Select 15,'2009-01-28' union all
Select 20,'2009-02-12'

--Roll SQL Engine


Select t.RequiredQty,t.RequiredDate,m.CumulativeTotal
from @T t
OUTER APPLY
(select Sum(RequiredQty) as CumulativeTotal
 from @T
 Where RequiredQty <= t.RequiredQty)m

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 13:46:48

Did it work? It insert all values including duplicates.

Lurmis
Starting Member

4 Posts

Posted - 2009-02-11 : 14:44:23

Not really.

Basically what I am trying to do is figure out when an inventory item will be depleted. Say I have 50 pcs of item XYZ. If there are 3 orders out there with demands of 20, 25, and 35; the 3rd order will be short unless we order more of item XYZ. I have a query that returns the demands ordered by required date, ascending. I am trying to get a query that will tell me what required date will be the one on which I will run short of parts.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 14:46:27

quote:
Originally posted by Lurmis

Not really.

Basically what I am trying to do is figure out when an inventory item will be depleted. Say I have 50 pcs of item XYZ. If there are 3 orders out there with demands of 20, 25, and 35; the 3rd order will be short unless we order more of item XYZ. I have a query that returns the demands ordered by required date, ascending. I am trying to get a query that will tell me what required date will be the one on which I will run short of parts.

But it was not explained in previous post. Can we get sample data and expected output?

Lurmis
Starting Member

4 Posts

Posted - 2009-02-11 : 15:32:03

Sorry. I did find something from a blog by Robin Hames about generating a running total. I modified it to suit me and it is working. Here is a link.

http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 17:05:50

Well, he is wrong in on at least two things.
I posted a comment for him to change his article.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources