Alternate of lead lag function in SQL Server 2008

by

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

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Unable to Subtract Dates SQL Server
  3. Insert an image in a column of a table that already has 5 columns
  4. what is the reason behind this error in case statement
  5. CROSS JOIN vs INNER JOIN in SQL
  6. How to cast the DateTime to Time
  7. How can I schedule a job to run a SQL query daily?
  8. Reset identity seed after deleting records in SQL Server
  9. How to avoid the “divide by zero” error in SQL?
  10. SET versus SELECT when assigning variables?
  11. Multiple INSERT statements vs. single INSERT with multiple VALUES
  12. Generate Dates between date ranges
  13. Real life example, when to use OUTER / CROSS APPLY in SQL
  14. How to print VARCHAR(MAX) using Print Statement?
  15. Default row order in SELECT query – SQL Server 2008 vs SQL 2012
  16. Common Table Expression, why semicolon?
  17. T-SQL split string based on delimiter
  18. SQL Server 2008: how do I grant privileges to a username?
  19. Splitting delimited values in a SQL column into multiple rows
  20. How do I find duplicates across multiple columns?
  21. Execute Dynamic Query with go in sql
  22. TransactSQL to run another TransactSQL script
  23. How can I get a list of element names from an XML value in SQL Server
  24. Stored procedure that exports data into csv files only exports to one file
  25. SQL Server: should I use information_schema tables over sys tables?
  26. Comma Separated values with SQL Query
  27. TSQL – How to use GO inside of a BEGIN .. END block?
  28. SQL take just the numeric values from a varchar
  29. How to execute a stored procedure inside a select query
  30. Using CASE Statement inside IN Clause

Leave a Comment