0

I have a SQL Server database. 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..

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • How do you define the *previous* and *next* rows? Remember that relational tables do not have inherent ordering. You need to provide us with a sorting criteria. – The Impaler Aug 26 '22 at 17:36
  • ORDER BY DATE ASC listening criteria –  Aug 26 '22 at 17:38
  • You really should avoid `NOLOCK`, it has serious data integrity implications. – Charlieface Aug 28 '22 at 00:25
  • i knowed NOLOCK method. same time multiple insert-update-select query protecting running data? –  Aug 28 '22 at 17:52

1 Answers1

0

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
The Impaler
  • 45,731
  • 9
  • 39
  • 76