comparing and adding row data

[Deepa06]

I have to compare 'MOVINGAVERAGE' column of current row with next row until 'MOVINGAVERAGE' column of next row has greater value than previous column.

 

[kray4660]

Depending on your version of SQL, Lead and Lag will allow you to look a next and previous row values. Otherwise you have to set up a Cursor (I hope not).

 

[Mike Lewis]

Depending on your version of SQL ....

I believe both Lead and Lag were introduced in SQL Server 2012. Other implementations (such as MySQL and Oracle) also support these functions.

They are really useful, because SQL does not normally have the concept of "next row" or "previous row" (unlike Visual FoxPro, where there is the concept of a record pointer). Without these functions, you could probably do it with some sort of self-join: first, getting rows where the relevant value is greater than that of the base row; then getting the first (or last) row in that result set, in a given order. But I won't try to give an example.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads