declare cursor previous and current element by sql server-CodePudding

I have a SQL Server database. Here, I keep data as int type points. Then I will do another operation with these ids, but how can I perform a mathematical operation between the declare cursor and the previous and next element in a certain table?

The rough draft algorithm is as follows;

16 (1.rows point data)
20 (2.rows point data => ABS(20-16) => 4 )
18 (3.rows point data => ABS(18-20) => 2 )
30 (4.rows point data => ABS(30-18) => 12 )
55 (5.rows point data => ABS(55-30) => 15 )
29 (6.rows point data => ABS(29-55) => 21 )
32 (7.rows point data => ABS(32-29) => 3 )

I will set this as stored procedure in programmability.

Currently only show point data in t-sql code;

DECLARE @point as int
DECLARE example CURSOR FOR SELECT POINT FROM data with (nolock) ORDER BY DATE ASC
    OPEN example
    FETCH NEXT FROM example INTO @point
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    select @point
    FETCH NEXT FROM example INTO @point
    END
    CLOSE example
    DEALLOCATE example

Thank you for everything..

CodePudding user response：

You can use LAG() to peek at the previous row, according to an ordering criteria, as in lag(point) over(order by date).

For example:

select t.*,
  point - coalesce(lag(point) over(order by date), 0) as diff
from t