Harrynet


 

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




Re: Decrement values in an aditional column

Arnie Rowland


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

 Hint: 'Greater Than'

 







Re: Decrement values in an aditional column

lkh

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

ID Entry RunningTotal

1 500 500

2 -400 100

3 -300 -200

4 -200 -400

5 -100 -500







Re: Decrement values in an aditional column

Harrynet

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





Re: Decrement values in an aditional column

lkh

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.






Re: Decrement values in an aditional column

Arnie Rowland

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.




Re: Decrement values in an aditional column

Harrynet

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.





Re: Decrement values in an aditional column

lkh

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

ID Entry RunningTotal

1 500 NULL

2 -400 -900

3 -300 100

4 -200 100

5 -100 100