Decrement values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 10
e 4
I want to decrement this values in an aditional column

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

Thks for your help

Rgds

Harry

Try a variation of the solution provided to you in your previous post, "Accumulate values in an aditional column".

Hint: 'Greater Than'

|||

There are endless variations depending on table structure, data and what you want to do with the data. In a real world situation how would you know what to subtract from what in your example?

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?

|||

You example makes no sense.

For ID = 4 you are subtracting the original value of the preceding row but for the other rows you are subtracting the output of the prior calculation.

|||As I previously indicated, take the solution offered you to increment the values, and change the [LESS THAN] to a [GREATER THEN], and you may get what you seek.|||

Yes sorry I make a mistaked , what i want to know is that..It means how to subtracting the original value of the preceding row and I my example I do this with all rows in ID =2 the value mus tbe --> -400 - 500 = -900

ID = 3 --> -300 - (-400) = -300+400 = 100

ID = 4 --> -200-(-300)= -200+300 = 100

The table must be:

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-400) = 100

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

5 -100 -100 -(-200) = 100

Im really sorry for the confusion but Its important for me how to make it I try with "less than" "greather than" but it doenst work.

|||

So for each row you want to calculate a value that equals the Entry value for that row minus the Entry valaue for the preceding row.

UPDATE dbo.Balance

SET RunningTotal = b1.entry - b2.entry

FROM dbo.Balance b1

INNER JOIN

(

SELECT ID, entry

FROM dbo.Balance

)b2

ON b1.ID = b2.ID + 1

IDEntryRunningTotal

1500NULL

2-400-900

3-300100

4-200100

5-100100