Self-referencing a table for previous record matching user ID

Question

I'm trying to find the easiest way to calculate cycle times from SQL data. In the data source I have unique station ID's, user ID's, and a date/time stamp, along with other data they are performing.

What I want to do is join the table to itself so that for each date/time stamp I get: - the date/time stamp of the most recent previous instance of that user ID within 3 minutes or null - the difference between those two stamps (the cycle time = amount of time between records)

This should be simple but I can't wrap my brain around it. Any help?

Answer 1

Unfortunately SQL Server does not support date range specifications in window functions. I would recommend a lateral join here:

select 
    t.*, 
    t1.timestamp last_timestamp, 
    datediff(second, t1.timestamp, t.timestamp) diff_seconds
from mytable t
outer apply (
    select top(1) t1.*
    from mytable t1
    where 
        t1.user_id = t.user_id 
        and t1.timestamp >= dateadd(minute, -3, t.timestamp)
        and t1.timestamp < t.timestamp
    order by t1.timestamp desc
) t1

The subquery brings the most recent row within 3 minutes for the same user_id (or an empty resultset, if there is no row within that timeframe). You can then use that information in the outer query to display the corresponding timestamp, and compute the difference with the current one.