How do I get the difference between current cell with previous cell?

Question

4.00/5 (1 vote)

See more:

How do I go about getting the difference between the previous cell and current cell and then put the answer in another cell in another column?

I have a CSV file that will eventually get uploaded into an Access table. I would like to do the work in a datatable before pushing it to the Access table.

Here's what I would have

columnA	columnB
20
50	30
60	10
45	-15

ColumnB shows the difference between the rows with the previous row.

Here's a little more to the question.

In columnA we see 20 and then below it we see 50. Beside the 50 I want it to be the difference of the 50 and 20 showing in ColumnB 30.

In the next series we see 50 and the 60 below it in ColumnA. Besides the 60 I want it to show the difference between 50 and 60 thus adding 10 to ColumnB.

Lastly, we see 60 and then 45 below it thus giving us the difference between 60 and 45 which is -15.

Basically I have a tonne of coordinates that I need to know the difference between each one so I can do other calculations based on the differences of the point before.

Would someone please point me in the right direction?

Thanks

Mike

Posted 6-Oct-12 15:55pm

mzrax

Updated 7-Oct-12 13:05pm

v2

Add a Solution

Comments

Thilina Chandima 6-Oct-12 22:37pm

please explain more. what you really want.. I mean do you wan to get to columns difference or two rows difference...?

Sandeep Mewara 7-Oct-12 1:33am

Example says, two rows difference of a given column. Here, columnB values are based on columnA value - they are difference of current & previous values.

Herman<T>.Instance 7-Oct-12 2:26am

Is there an ID column or datetime column that can be used which can be used in an Order by but the values stay in the order given? Just the numbers is too small for CTE, Row_Number() and query.

Thilina Chandima 7-Oct-12 2:55am

why can't you just use a loop and do the calculation...? is it impossible to do that way...?

mzrax 7-Oct-12 19:27pm

I know that I have to loop and then do a calculation but I don't know how. Can you point me in the right direction or do you have an example??

skydger 7-Oct-12 12:29pm

Could you please give an example of tables' contents?

mzrax 7-Oct-12 19:27pm

I've added more to my question. Any ideas??

2 solutions

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)

Kuthuparakkal · Accepted Answer · 2012-10-07T15:13:00

Solution 1

May not work if you got repeating items, but definitely a point where you can build up.

SQL

SELECT X.ColA, ISNULL(Y.ColA,0)-X.ColA
FROM
SELECT ColA, ROW_NUMBER() OVER(ORDER BY ColA) AS RID
FROM TableA
)AS X
LEFT OUTER JOIN 
(
SELECT ColA, (1 + ROW_NUMBER() OVER(ORDER BY ColA)) AS RID
FROM TableA
)AS Y
ON X.RID = Y.RID

Posted 7-Oct-12 15:13pm

Kuthuparakkal

Comments

damodara naidu betha 8-Oct-12 2:33am

Good one.But this needs be corrected. To get difference, replace ISNULL(Y.ColA,0)-X.ColA With ISNULL(X.ColA,0)-Y.ColA, and you are taking row number order by ColA. This sorts the column values in ascending order. So the query doesn't produce desired result.

mzrax 8-Oct-12 22:58pm

I have to say your solution does the trick too. Again, I'm stumped because I thought I was going to take my project in a very specific way and now that both of you have given excellent solutions I'm going to have to rethink everything.

I just wanted to say that I added an ID column and did the order by the ID and therefore I shouldn't have to worry about duplicates.

I wanted to thank you too and a big thumbs up. Now I have to figure out how to integrate this with my C# project.

Thanks
M

damodara naidu betha · Accepted Answer · 2012-10-07T20:21:00

Hi Mike...

Try this code block ...

SQL

  Declare @MyTestTable table
(
id Int,
PointValue int
)

--—Insert sample data
-- NOTE : Here Id should  be unique and in order.

Insert into @MyTestTable Values (1,150)
Insert into @MyTestTable Values (2,350)
Insert into @MyTestTable Values (3,500)
Insert into @MyTestTable Values (4,100)
Insert into @MyTestTable Values (5,150);

--Create CTE
With tblDifference as
(
Select Row_Number() OVER (Order by id) as RowNumber,id,PointValue from @MyTestTable
)

--Actual Query
Select Cur.PointValue as CurrentValue, ISNULL(Prv.PointValue,0) as PreviousValue,ISNULL(Cur.PointValue-Prv.PointValue,0) as Difference from
tblDifference Cur Left Outer Join tblDifference Prv
On Cur.RowNumber=Prv.RowNumber+1
Order by Cur.id