Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

declare cursor previous and current element by sql server

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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..

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading