Re: Finding and Converting negative numbers

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

IF there are no gaps in the ID field then this is simple.

If there are gaps, then this becomes a more complex and also slower.

First Case:

SELECT T.Value,
, T.Value - T1.Value as Result
FROM YourTable as T LEFT JOIN YourTable as T1
ON T.ID = (T1.ID -1 )


Second Case
SELECT T.Value
, T.Value - (SELECT T2.Value
FROM YourTable as T2
WHERE T2.ID = (Select Min(T1.ID)
FROM YourTable as T1
WHERE T1.ID > T.ID)) as Result
FROM YourTable as T


"steenman2" <asteenrod@xxxxxxxxx> wrote in message
news:1149511394.202604.297860@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Each row has an ID number that is in accending order.


ID Value Result
1 -70 0
2 -70 1.02
3 -68.98 1.09
4 -67.89 3.11
5 -7 .71
6 -69.29 1.52
7 -67.77



.