How to Compare Adjacent Rows in SQL Server 2008 Without LEAD and LAG?

Alternatives to LEAD and LAG Functions in SQL Server 2008

Problem:

SQL Server 2008 does not support the LEAD and LAG functions. You need to determine how to compare the current row with the value in the next row.

Solution:

One alternative is to perform a self-join:

SELECT t.*
FROM table t JOIN
     table tnext
     ON t.id = tnext.id - 1 AND
        t.StatusId = 1 AND
        tnext.StatusId = 6 AND
        DATEDIFF(SECOND, t.MinStartTime, tnext.MinStartTime) < 60;

This query will return rows where the current row's status is 1, the next row's status is 6, and the time difference between the two rows is less than 60 seconds.

If you require an exact minute match, you can modify the query:

SELECT t.*
FROM table t JOIN
     table tnext
     ON t.id = tnext.id - 1 AND
        t.StatusId = 1 AND
        tnext.StatusId = 6 AND
        DATEDIFF(SECOND, t.MinStartTime, tnext.MinStartTime) < 60 AND
        DATEPART(MINUTE, t.MinStartTime) = DATEPART(MINUTE, tnext.MinStartTime);

The above is the detailed content of How to Compare Adjacent Rows in SQL Server 2008 Without LEAD and LAG?. For more information, please follow other related articles on the PHP Chinese website!