Accumulate values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 5
e 4

I want to accumulate this values in an aditional column

CLIENT Value ACUM
a 12 12
b 11 12 + 11 = 23
c 8 23 + 8 = 31
d 5 31 + 5 = 36
e 4 36 + 4 = 40

Thks for your help

Rgds

Harry

CREATE TABLE dbo.RunningTotal

(

Entry int

,RunningTotal int

)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(100,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(200,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(300,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(400,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(500,NULL)

UPDATE dbo.RunningTotal

SET RunningTotal = RT2.RunningTotal

FROM dbo.RunningTotal RT1

INNER JOIN

(

SELECT Entry

,(SELECT SUM(Entry) FROM dbo.RunningTotal WHERE Entry <= rt.Entry) As RunningTotal

FROM dbo.RunningTotal rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.RunningTotal

Resultset:

Entry RunningTotal

100 100

200 300

300 600

400 1000

500 1500

|||

What would I have to do if i want to decrement this values?

CLIENT Value ACUM
a 12 12
b 11 11-12 = -1
c 8 8-11 = -3
d 10 10-8 = 2
e 4 4 -10 = -6

Hope to recieve some news soon

Rgds & a lot of thks

Harry

|||

There are endless variations depending on table structure, data and what you want to do with the data.

Here is one other example:

CREATE TABLE dbo.Balance

(

ID int

,Entry int

,RunningTotal int

)

TRUNCATE TABLE dbo.Balance

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(1,500,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(2,-400,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(3,-300,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(4,-200,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(5,-100,NULL)

UPDATE dbo.Balance

SET RunningTotal = RT2.RunningTotal

FROM dbo.Balance RT1

INNER JOIN

(

SELECT Entry

,(SELECT -SUM(-Entry) FROM dbo.Balance WHERE ID <= rt.ID ) As RunningTotal

FROM dbo.Balance rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.Balance

IDEntryRunningTotal

1500500

2-400100

3-300-200

4-200-400

5-100-500

|||

I try this , but the values only are growing

the table with this input values must be

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-900) = 600

4 -200 -200-(-300) = 100

5 -100 -100 - 100 = -200

How coud I do this?

|||

That is a good method and works perfectly for relatively small number of records

Unfortunatly for a large number it is too slow ,,, :(:(:( do you have another faster method ?

I'm a little desparate

Thanks

|||Yes. do it in your front end application or reporting tool.