An arithmetic operation on the records in one field

Question

1.00/5 (1 vote)

See more:

Hello all
I have a problem
I would like to work as in the picture below ...
in sql server Query.

Thanks for your help to me.

http://www14.0zz0.com/2014/05/16/09/845618102.jpg[

[Edit CHill60]
For those not willing, or unable to, follow the link provided the image essentially shows the following

RowNumber	Column1	Column2 (How calculated)
1		614	614	Fixed value
2		187-	427	= 614-187
3		5-	422	= 427-5
4		21-	401	= 422-21
5		8-	393	= 401-8
6		30-	363	= 393-30

In other words the OP wants to calculate a figure based on the previous row.

What I mean is:
This query returns 3 columns.

Column1 in row 1 is fixed value (614) and put it to the row 1 in column2 and then

Row 2 in column2 = 614 -187=427

Row 3 in column2 =427 -5=422
....

I'm sorry for my English simple.

Thanks for All

Posted 15-May-14 23:51pm

sYsOm

Updated 16-May-14 1:20am

v3

Add a Solution

Comments

CHill60 16-May-14 6:22am

What have you tried so far? Do you have to use this table schema? (it is awful and the data is inconsistent in pattern)

sYsOm 17-May-14 3:10am

What I mean is:
This query returns 3 columns.

Column1 in row 1 is fixed value (614) and put it to the row 1 in column2 and then

Row 2 in column2 = 614 -187=427

Row 3 in column2 =427 -5=422
....

I'm sorry for my English simple.

Thank you

appoos 2-Jun-14 9:25am

What is you table structure. Do you have only one field in your table?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-06-02T09:09:00

Test it:

SQL

DECLARE @tmp TABLE (RowNo INT, Col1 INT)

INSERT INTO @tmp (RowNo, Col1)
VALUES(1, 614), (2, -187), (3, -5),
(4, -21), (5, -8), (6, -30)

SELECT t1.RowNo, t1.Col1, SUM(t2.Col1) AS CurrValue
FROM @tmp AS t1 INNER JOIN @tmp AS t2 ON t1.RowNo >= t2.RowNo
GROUP BY t1.RowNo, t1.Col1
ORDER BY t1.RowNo

Result:

RowNo  Col1      CurrVal
1      614       614
2      -187      427
3      -5        422
4      -21       401
5      -8        393
6      -30       363