Finding the difference between variables

[hgus393]

Hi,
Sorry for the title of the thread. What I am trying to do is the following
I have three columns
Name, Date, Number

What I need to do is find the difference in the number column where the Name is the same but the date is not ex:

Bob 2013-12-12 123
Bob 2013-12-15 456
Jane 2013-12-14 789
Jane 2013-12-25 987

So what I need to do is to get a result that is independent of date i.e.
Bob (456-123)
Jane (987 - 789)

Does anyone have clue how to do this in SQL?
Cheer
Bob

 

[Galaxiom]

Use a self join to an alias of the same table. In the query designer simply drag the table in twice and join on the Name field.

Then derive a field with the subtraction expression.

This will return both directions of the subtraction. This can be avoided by putting a condition on the join (must be done in SQL view) or with a Where clause that returns records with only the lesser of the two values on the alias.

 

[hgus393]

Thank you very much.

The final result if someone is interested:

SELECT Table1.Name_, [Table1].[Value]-[Table1_1].[Value] AS ValueDiff, Table1.Date_
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.Name_ = Table1_1.Name_
where Table1.Date_ > Table1_1.Date_
ORDER BY Table1.Date_;

Cheers

BOB