Bram Bram - 7 months ago 7
SQL Question

Find time period in list of status changes (per status)

I have a list of moments a specific user does something on a specific workstation:

station timestamp
TC61879 2016-03-25 09:34:40.000
TC61879 2016-03-25 09:38:36.000
TC61879 2016-03-25 10:01:17.000
TC61879 2016-03-25 10:02:10.000
TC61879 2016-03-25 10:04:01.000
TC61879 2016-03-25 10:04:43.000
TC61879 2016-03-25 10:05:49.000
TC61879 2016-03-25 10:06:00.000
TC61878 2016-03-25 10:08:05.000*
TC61879 2016-03-25 10:09:41.000
TC61879 2016-03-25 10:10:40.000
TC61879 2016-03-25 10:35:50.000
TC61879 2016-03-25 10:37:57.000
TC61879 2016-03-25 10:38:21.000
TC61879 2016-03-25 10:39:34.000
TC61879 2016-03-25 10:40:59.000
TC61879 2016-03-25 10:41:20.000
TC61879 2016-03-25 10:42:21.000
TC61879 2016-03-25 10:44:05.000
TC61879 2016-03-25 10:44:17.000
TC61879 2016-03-25 10:46:25.000
TC61879 2016-03-25 10:47:48.000
TC61879 2016-03-25 10:49:03.000
TC61879 2016-03-25 10:51:31.000
TC61879 2016-03-25 10:51:58.000
TC61875 2016-03-25 10:52:42.000*
TC61875 2016-03-25 10:53:49.000*
TC61879 2016-03-25 10:53:57.000
TC61879 2016-03-25 11:36:58.000
TC61879 2016-03-25 11:37:37.000
TC61879 2016-03-25 11:38:45.000
TC61879 2016-03-25 11:40:08.000
TC61879 2016-03-25 11:41:46.000
TC61879 2016-03-25 11:43:43.000
TC61879 2016-03-25 11:44:49.000
TC61879 2016-03-25 11:46:06.000
TC61879 2016-03-25 11:48:17.000
TC61879 2016-03-25 11:50:22.000
TC61879 2016-03-25 11:52:06.000
TC61879 2016-03-25 11:52:22.000
TC61879 2016-03-25 11:53:07.000
TC61879 2016-03-25 11:55:29.000
TC61879 2016-03-25 11:55:49.000
TC61879 2016-03-25 11:56:24.000
TC61879 2016-03-25 11:57:20.000
TC61879 2016-03-25 11:57:55.000
TC61879 2016-03-25 11:58:15.000
TC61879 2016-03-25 11:59:05.000
TC61879 2016-03-25 11:59:17.000
TC61878 2016-03-25 11:59:52.000*
TC61879 2016-03-25 12:00:08.000
TC61879 2016-03-25 12:01:04.000
TC61879 2016-03-25 12:01:36.000
TC61878 2016-03-25 12:04:42.000*
TC61879 2016-03-25 12:04:46.000
TC61879 2016-03-25 12:05:37.000
TC61878 2016-03-25 12:07:37.000*
TC61879 2016-03-25 12:07:47.000
TC61879 2016-03-25 12:08:36.000
TC61879 2016-03-25 12:10:16.000
TC61879 2016-03-25 12:11:30.000


Asterisk is when a user did something on a different workstation than his default one. What is need is a list of from-to per workstation, like this:

Station From To
TC61879 2016-03-25 09:34:40.000 2016-03-25 10:08:05.000
TC61878 2016-03-25 10:08:05.000 2016-03-25 10:09:41.000
TC61879 2016-03-25 10:09:41.000 2016-03-25 10:52:42.000
TC61875 2016-03-25 10:52:42.000 2016-03-25 10:53:57.000
TC61879 2016-03-25 10:53:57.000 2016-03-25 11:59:52.000
TC61878 2016-03-25 11:59:52.000 2016-03-25 12:00:08.000
TC61879 2016-03-25 12:00:08.000 2016-03-25 12:04:42.000
TC61878 2016-03-25 12:04:42.000 2016-03-25 12:04:46.000
TC61879 2016-03-25 12:04:46.000 2016-03-25 12:07:37.000
TC61878 2016-03-25 12:07:37.000 2016-03-25 12:07:47.000
TC61879 2016-03-25 12:07:47.000 2016-03-25 12:11:30.000


I have tried various things with window functions and joins, but I keep having the problem that, when a users switches from one workstation to another and back the data does not reflect when the user did something on the other workstation.

select station from resulttable where @datetime between from and to


This query should only give one row as a result

Eventually I want to use this data do determine where a user in any given datetime.

How should I approach this?

Answer

You want to assign groups based on changes in a column (ordered by time). There is a "trick" using the difference of row numbers that makes this quite easy:

select station, min(timestamp), max(timestamp)
from (select t.*,
             row_number() over (order by timestamp) as seqnum_t,
             row_number() over (partition by station order by timestamp) as seqnum_wst
      from t
     ) t
group by (seqnum_t - seqnum_wst), station;

If you look at the sequential numbers that are generated, the first is enumerating the rows. The second enumerates the rows within each station. When the rows are sequential, then the difference is constant.