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

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

Adam
1#
Adam Published in 2017-11-13 14:43:35Z

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
Used_By_Already
2#
Used_By_Already Reply to 2017-11-13 22:07:38Z

For SQL Server 2012 an later, use LEAD(). For SQL 2008 you can use ROW_NUMBER() and a self join that uses the generated row number offset by 1 to align the "next values" to the current row. Both techniques are shown below for comparison. A common table expression (CTE) is used to facilitate the self join.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1
    ([idvisit] int, [user_id] varchar(8), [visit_date] date, [visit_first_action_time] datetime, [visit_last_action_time] datetime)
;

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

Query using LEAD():

select
        idvisit
      , user_id
      , visit_date
      , visit_first_action_time
      , visit_last_action_time
      , lead (visit_first_action_time) over (partition by user_id, visit_date 
                                             order by visit_first_action_time ASC, visit_last_action_time ASC) as start_idle_time 
      , datediff(ss,visit_last_action_time
                 , lead (visit_first_action_time) over (partition by user_id, visit_date 
                                                        order by visit_first_action_time ASC, visit_last_action_time ASC)
                ) as secs_between
from Table1
order by user_id, visit_date, visit_first_action_time, visit_last_action_time
;

Results:

| idvisit |  user_id | visit_date | visit_first_action_time | visit_last_action_time |      start_idle_time | secs_between |
|---------|----------|------------|-------------------------|------------------------|----------------------|--------------|
|    4663 | abdifa01 | 2017-11-07 |    2017-11-07T10:11:52Z |   2017-11-07T12:09:01Z | 2017-11-07T12:54:17Z |         2716 |
|    4741 | abdifa01 | 2017-11-07 |    2017-11-07T12:54:17Z |   2017-11-07T20:11:51Z |               (null) |       (null) |
|    4906 | abdifa01 | 2017-11-08 |    2017-11-08T07:58:05Z |   2017-11-08T19:52:09Z |               (null) |       (null) |
|    5140 | abdifa01 | 2017-11-09 |    2017-11-09T07:14:22Z |   2017-11-09T18:13:04Z |               (null) |       (null) |
|    5732 | abdifa01 | 2017-11-13 |    2017-11-13T07:23:41Z |   2017-11-13T12:54:10Z |               (null) |       (null) |
|    5183 | abirio00 | 2017-11-09 |    2017-11-09T08:08:23Z |   2017-11-09T08:08:23Z | 2017-11-09T08:42:23Z |         2040 |
|    5204 | abirio00 | 2017-11-09 |    2017-11-09T08:42:23Z |   2017-11-09T09:19:50Z | 2017-11-09T09:51:40Z |         1910 |
|    5236 | abirio00 | 2017-11-09 |    2017-11-09T09:51:40Z |   2017-11-09T10:20:54Z | 2017-11-09T10:57:29Z |         2195 |
|    5252 | abirio00 | 2017-11-09 |    2017-11-09T10:57:29Z |   2017-11-09T15:48:59Z |               (null) |       (null) |
|    5436 | abirio00 | 2017-11-10 |    2017-11-10T08:24:53Z |   2017-11-10T14:41:17Z | 2017-11-10T15:21:07Z |         2390 |
|    5554 | abirio00 | 2017-11-10 |    2017-11-10T15:21:07Z |   2017-11-10T15:21:07Z | 2017-11-10T15:21:07Z |            0 |
|    5553 | abirio00 | 2017-11-10 |    2017-11-10T15:21:07Z |   2017-11-10T16:07:02Z |               (null) |       (null) |
|    5620 | abirio00 | 2017-11-11 |    2017-11-11T09:27:21Z |   2017-11-11T12:45:07Z | 2017-11-11T14:06:32Z |         4885 |
|    5643 | abirio00 | 2017-11-11 |    2017-11-11T14:06:32Z |   2017-11-11T17:01:38Z |               (null) |       (null) |
|    5681 | abirio00 | 2017-11-12 |    2017-11-12T10:31:00Z |   2017-11-12T16:12:01Z |               (null) |       (null) |
|    5760 | abirio00 | 2017-11-13 |    2017-11-13T08:14:17Z |   2017-11-13T12:59:31Z |               (null) |       (null) |

Query using ROW_NUMBER() and CTE:

with myCTE as (
      select 
             table1.*
           , row_number()  over (partition by user_id, visit_date 
                    order by visit_first_action_time ASC, visit_last_action_time ASC) as rn
      from table1 
      )
select
        t1.idvisit
      , t1.user_id
      , t1.visit_date
      , t1.visit_first_action_time
      , t1.visit_last_action_time
      , t2.visit_first_action_time as start_idle_time 
      , datediff(ss,t1.visit_last_action_time, t2.visit_first_action_time)  as secs_between
from myCTE t1
left join myCTE t2 on t1.user_id = t2.user_id and t1.visit_date = t2.visit_date
                  and t1.rn = t2.rn-1
order by t1.user_id, t1.visit_date, t1.visit_first_action_time, t1.visit_last_action_time
;                  

Results:

| idvisit |  user_id | visit_date | visit_first_action_time | visit_last_action_time |      start_idle_time | secs_between |
|---------|----------|------------|-------------------------|------------------------|----------------------|--------------|
|    4663 | abdifa01 | 2017-11-07 |    2017-11-07T10:11:52Z |   2017-11-07T12:09:01Z | 2017-11-07T12:54:17Z |         2716 |
|    4741 | abdifa01 | 2017-11-07 |    2017-11-07T12:54:17Z |   2017-11-07T20:11:51Z |               (null) |       (null) |
|    4906 | abdifa01 | 2017-11-08 |    2017-11-08T07:58:05Z |   2017-11-08T19:52:09Z |               (null) |       (null) |
|    5140 | abdifa01 | 2017-11-09 |    2017-11-09T07:14:22Z |   2017-11-09T18:13:04Z |               (null) |       (null) |
|    5732 | abdifa01 | 2017-11-13 |    2017-11-13T07:23:41Z |   2017-11-13T12:54:10Z |               (null) |       (null) |
|    5183 | abirio00 | 2017-11-09 |    2017-11-09T08:08:23Z |   2017-11-09T08:08:23Z | 2017-11-09T08:42:23Z |         2040 |
|    5204 | abirio00 | 2017-11-09 |    2017-11-09T08:42:23Z |   2017-11-09T09:19:50Z | 2017-11-09T09:51:40Z |         1910 |
|    5236 | abirio00 | 2017-11-09 |    2017-11-09T09:51:40Z |   2017-11-09T10:20:54Z | 2017-11-09T10:57:29Z |         2195 |
|    5252 | abirio00 | 2017-11-09 |    2017-11-09T10:57:29Z |   2017-11-09T15:48:59Z |               (null) |       (null) |
|    5436 | abirio00 | 2017-11-10 |    2017-11-10T08:24:53Z |   2017-11-10T14:41:17Z | 2017-11-10T15:21:07Z |         2390 |
|    5554 | abirio00 | 2017-11-10 |    2017-11-10T15:21:07Z |   2017-11-10T15:21:07Z | 2017-11-10T15:21:07Z |            0 |
|    5553 | abirio00 | 2017-11-10 |    2017-11-10T15:21:07Z |   2017-11-10T16:07:02Z |               (null) |       (null) |
|    5620 | abirio00 | 2017-11-11 |    2017-11-11T09:27:21Z |   2017-11-11T12:45:07Z | 2017-11-11T14:06:32Z |         4885 |
|    5643 | abirio00 | 2017-11-11 |    2017-11-11T14:06:32Z |   2017-11-11T17:01:38Z |               (null) |       (null) |
|    5681 | abirio00 | 2017-11-12 |    2017-11-12T10:31:00Z |   2017-11-12T16:12:01Z |               (null) |       (null) |
|    5760 | abirio00 | 2017-11-13 |    2017-11-13T08:14:17Z |   2017-11-13T12:59:31Z |               (null) |       (null) |
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO