CODEWITHSUNDEEP
sqlsql-serverdatejoinwindow-functions
BLHolliday
BLHolliday

Reputation: 23

Self-referencing a table for previous record matching user ID

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?

Upvotes: 0

Views: 242

Answers (2)

dnoeth
dnoeth

Reputation: 60462

Simply calculate the difference of the current and the LAG timestamp, if it's more than three minutes return NULL instead: 

with cte as
 (
   select 
      t.*
     ,datediff(second, timestamp, lag(timestamp) over (partition by user_id order by timestamp) as diff_seconds
   from mytable as t
 )
select cte.*
  ,case when diff_seconds <= 180 then diff_seconds end
from cte

Upvotes: 0

GMB
GMB

Reputation: 222442

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.

Upvotes: 1

Related Questions