Home Calculating the time between start/end time intervals in SQL Server 2008
Reply: 0

Calculating the time between start/end time intervals in SQL Server 2008

user2112
1#
user2112 Published in June 23, 2018, 9:18 pm

A user visit tracking data set records the first and last action time of every visit:

idvisit user_id     visit_date  visit_first_action_time visit_last_action_time
5732    abdifa01    2017-11-13  2017-11-13 07:23:41     2017-11-13 12:54:10
5140    abdifa01    2017-11-09  2017-11-09 07:14:22     2017-11-09 18:13:04
4906    abdifa01    2017-11-08  2017-11-08 07:58:05     2017-11-08 19:52:09
4663    abdifa01    2017-11-07  2017-11-07 10:11:52     2017-11-07 12:09:01
4741    abdifa01    2017-11-07  2017-11-07 12:54:17     2017-11-07 20:11:51
5760    abirio00    2017-11-13  2017-11-13 08:14:17     2017-11-13 12:59:31
5681    abirio00    2017-11-12  2017-11-12 10:31:00     2017-11-12 16:12:01
5620    abirio00    2017-11-11  2017-11-11 09:27:21     2017-11-11 12:45:07
5643    abirio00    2017-11-11  2017-11-11 14:06:32     2017-11-11 17:01:38
5553    abirio00    2017-11-10  2017-11-10 15:21:07     2017-11-10 16:07:02
5554    abirio00    2017-11-10  2017-11-10 15:21:07     2017-11-10 15:21:07
5436    abirio00    2017-11-10  2017-11-10 08:24:53     2017-11-10 14:41:17
5183    abirio00    2017-11-09  2017-11-09 08:08:23     2017-11-09 08:08:23
5204    abirio00    2017-11-09  2017-11-09 08:42:23     2017-11-09 09:19:50
5236    abirio00    2017-11-09  2017-11-09 09:51:40     2017-11-09 10:20:54
5252    abirio00    2017-11-09  2017-11-09 10:57:29     2017-11-09 15:48:59

How would you produce a similar dataset using SQL query which displays the idle time between above records for the same user_id on the same visit_date ?

user_id     visit_date  start_idle_time        finish_idle_time
abdifa01    2017-11-07  2017-11-07 12:09:01    2017-11-07 12:54:17
abirio00    2017-11-11  2017-11-11 12:45:07    2017-11-11 14:06:32
abirio00    2017-11-10  2017-11-11 14:41:17    2017-11-11 15:21:07
abirio00    2017-11-09  2017-11-09 08:08:23    2017-11-09 08:42:23
abirio00    2017-11-09  2017-11-09 09:19:50    2017-11-09 09:51:40
abirio00    2017-11-09  2017-11-09 10:20:54    2017-11-09 10:57:29
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.318686 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO