Alternate of lead lag function in SQL Server 2008

Question

I want to compare the current row with a value in the next row. SQL has LEAD and LAG functions to get the next and previous values but I can not use them because I am using SQL Server 2008.

So how do I get this?

I have table with output

+----+-------+-----------+-------------------------+
| Id | ActId |  StatusId |       MinStartTime      |
+----+-------+-----------+-------------------------+
| 1  |  42   | 1         | 2014-02-14 11:17:21.203 |
| 2  |  42   | 1         | 2014-02-14 11:50:19.367 |
| 3  |  42   | 1         | 2014-02-14 11:50:19.380 |
| 4  |  42   | 6         | 2014-02-17 05:25:57.280 |
| 5  |  42   | 6         | 2014-02-19 06:09:33.150 |
| 6  |  42   | 1         | 2014-02-19 06:11:24.393 |
| 7  |  42   | 6         | 2014-02-19 06:11:24.410 |
| 8  |  42   | 8         | 2014-02-19 06:44:47.070 |
+----+-------+-----------+-------------------------+

What I want to do is if the current row status is 1 and the next row status is 6 and both times are the same (up to minutes) then I want to get the row where the status is 1.

Eg: Id 6 row has status 1 and Id 7 row has status 6 but both times are the same ie. 2014-02-19 06:11

So I want to get this row or id for status 1 ie. id 6

Answer 1

In your case, the ids appear to be numeric, you can just do 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 isn't quite the same minute. It is within 60 seconds. Do you actually need the same calendar time minute? If so, you can do:

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);