How to count users logged with duration per date and 15 min interval

Question

I want to determine the connected users by date and 15 min intervals, based on login and logout Date Time columns and sum the duration (in seconds) of that event depending on the login time and actual interval. In some cases, users are connected to the system for more than 10 hours, and connection may finish on the next day.

So I have an intervals table from 00:00 to 23:59 in 15 mins intervals and other table with login data.

I've tried to use both tables to get the login event for each interval on intervals table but it didn't work.

Here's the sample data

DECLARE @events TABLE
(
    userId VARCHAR(10)
    , loginDate DATE
    , loginTime TIME(0)
    , logoutDate DATE
    , logoutTime TIME(0)
)

INSERT INTO @events 
SELECT '1010862178', '2019-08-25', '06:56:59', '2019-08-25', '21:51:56'
UNION ALL SELECT '1010862178', '2019-08-25', '21:51:56', '2019-08-26', '13:56:24'
UNION ALL SELECT '1016063857', '2019-08-25', '05:56:10', '2019-08-25', '14:14:33'
UNION ALL SELECT '1016063857', '2019-08-25', '14:14:33', '2019-08-26', '13:58:13'

This is the events table

and the results should be like this:

I hope you guys can help me! Thanks in advance.

EDIT: the results table should start like this:

date        interval    usersConnected  duration
2019-08-25  05:45:00    1               230
2019-08-25  06:00:00    1               900
2019-08-25  06:15:00    1               900
2019-08-25  06:30:00    1               900
2019-08-25  06:45:00    2               1081

Ed Romero · Accepted Answer

OK after a few hours of writing queries, finally I found a solution, maybe it is not an elegant solution but it works, so now the challenge is to solve this with less code.

Here´s the answer.

DECLARE @events TABLE
(
    userId VARCHAR(10)
    , loginDate DATE
    , loginTime TIME(0)
    , logoutDate DATE
    , logoutTime TIME(0)
)

INSERT INTO @events 
SELECT '1010862178', '2019-08-25', '06:56:59', '2019-08-25', '21:51:56'
UNION ALL SELECT '1010862178', '2019-08-25', '21:51:56', '2019-08-26', '13:56:24'
UNION ALL SELECT '1016063857', '2019-08-25', '05:56:10', '2019-08-25', '14:14:33'
UNION ALL SELECT '1016063857', '2019-08-25', '14:14:33', '2019-08-26', '13:58:13'

/* Other tables are:
    dates table:  contains only two dates '2019-08-25' and '2019-08-26'
    intervals table: contains intervals from 00:00:00 to 23:45:00
*/

select 
    convert(date, i.intervalStart) [date]
    , convert(time(0), i.intervalStart) [interval]
    , count(distinct userId) usersConnected -- distinct prevent duplicates on overlaping login and logout hours
    , sum(case 
        when datediff(second, iif([login] > i.intervalStart, [login], i.intervalStart) , iif(logout < i.intervalEnd, logout, i.intervalEnd)) < 0 then 900
        else datediff(second, iif([login] > i.intervalStart, [login], i.intervalStart) , iif(logout < i.intervalEnd, logout, i.intervalEnd))
      end) duracion
 from (
 select userId
    , [login] = CAST(loginDate AS DATETIME) + CAST(loginTime AS DATETIME)
    , [logout] = CAST(logoutDate AS DATETIME) + CAST(logoutTime AS DATETIME)
 from @events ) c
 inner join (select intervalStart = CAST([date] AS DATETIME) + CAST([start] AS DATETIME)
        , intervalEnd = CAST([date] AS DATETIME) + CAST([end] AS DATETIME)
     from dbo.dates, dbo.intervals) m
     on c.[login] between  m.intervalStart and m.intervalEnd
 inner join (select
   intervalStart = CAST([date] AS DATETIME) + CAST([start] AS DATETIME)
        , intervalEnd = CAST([date] AS DATETIME) + CAST([end] AS DATETIME)
     from dbo.dates, dbo.intervals) i
 on i.intervalStart  between m.intervalStart and c.logout
 group by convert(date, i.intervalStart)
    , convert(time(0), i.intervalStart)
 order by 1, 2

I may change this using cross apply, but it works for now.

So, it returns this data:

date    interval    usersConnected  duracion
2019-08-25  05:45:00    1   230
2019-08-25  06:00:00    1   900
2019-08-25  06:15:00    1   900
2019-08-25  06:30:00    1   900
2019-08-25  06:45:00    2   1081
2019-08-25  07:00:00    2   1800
2019-08-25  07:15:00    2   1800
2019-08-25  07:30:00    2   1800
2019-08-25  07:45:00    2   1800
2019-08-25  08:00:00    2   1800
2019-08-25  08:15:00    2   1800
2019-08-25  08:30:00    2   1800
2019-08-25  08:45:00    2   1800
2019-08-25  09:00:00    2   1800
2019-08-25  09:15:00    2   1800
2019-08-25  09:30:00    2   1800
2019-08-25  09:45:00    2   1800
2019-08-25  10:00:00    2   1800
2019-08-25  10:15:00    2   1800
2019-08-25  10:30:00    2   1800
2019-08-25  10:45:00    2   1800
2019-08-25  11:00:00    2   1800
2019-08-25  11:15:00    2   1800
2019-08-25  11:30:00    2   1800
2019-08-25  11:45:00    2   1800
2019-08-25  12:00:00    2   1800
2019-08-25  12:15:00    2   1800
2019-08-25  12:30:00    2   1800
2019-08-25  12:45:00    2   1800
2019-08-25  13:00:00    2   1800
2019-08-25  13:15:00    2   1800
2019-08-25  13:30:00    2   1800
2019-08-25  13:45:00    2   1800
2019-08-25  14:00:00    2   1800
2019-08-25  14:15:00    2   1800
2019-08-25  14:30:00    2   1800
2019-08-25  14:45:00    2   1800
2019-08-25  15:00:00    2   1800
2019-08-25  15:15:00    2   1800
2019-08-25  15:30:00    2   1800
2019-08-25  15:45:00    2   1800
2019-08-25  16:00:00    2   1800
2019-08-25  16:15:00    2   1800
2019-08-25  16:30:00    2   1800
2019-08-25  16:45:00    2   1800
2019-08-25  17:00:00    2   1800
2019-08-25  17:15:00    2   1800
2019-08-25  17:30:00    2   1800
2019-08-25  17:45:00    2   1800
2019-08-25  18:00:00    2   1800
2019-08-25  18:15:00    2   1800
2019-08-25  18:30:00    2   1800
2019-08-25  18:45:00    2   1800
2019-08-25  19:00:00    2   1800
2019-08-25  19:15:00    2   1800
2019-08-25  19:30:00    2   1800
2019-08-25  19:45:00    2   1800
2019-08-25  20:00:00    2   1800
2019-08-25  20:15:00    2   1800
2019-08-25  20:30:00    2   1800
2019-08-25  20:45:00    2   1800
2019-08-25  21:00:00    2   1800
2019-08-25  21:15:00    2   1800
2019-08-25  21:30:00    2   1800
2019-08-25  21:45:00    2   1800
2019-08-25  22:00:00    2   1800
2019-08-25  22:15:00    2   1800
2019-08-25  22:30:00    2   1800
2019-08-25  22:45:00    2   1800
2019-08-25  23:00:00    2   1800
2019-08-25  23:15:00    2   1800
2019-08-25  23:30:00    2   1800
2019-08-25  23:45:00    2   1800
2019-08-26  00:00:00    2   1800
2019-08-26  00:15:00    2   1800
2019-08-26  00:30:00    2   1800
2019-08-26  00:45:00    2   1800
2019-08-26  01:00:00    2   1800
2019-08-26  01:15:00    2   1800
2019-08-26  01:30:00    2   1800
2019-08-26  01:45:00    2   1800
2019-08-26  02:00:00    2   1800
2019-08-26  02:15:00    2   1800
2019-08-26  02:30:00    2   1800
2019-08-26  02:45:00    2   1800
2019-08-26  03:00:00    2   1800
2019-08-26  03:15:00    2   1800
2019-08-26  03:30:00    2   1800
2019-08-26  03:45:00    2   1800
2019-08-26  04:00:00    2   1800
2019-08-26  04:15:00    2   1800
2019-08-26  04:30:00    2   1800
2019-08-26  04:45:00    2   1800
2019-08-26  05:00:00    2   1800
2019-08-26  05:15:00    2   1800
2019-08-26  05:30:00    2   1800
2019-08-26  05:45:00    2   1800
2019-08-26  06:00:00    2   1800
2019-08-26  06:15:00    2   1800
2019-08-26  06:30:00    2   1800
2019-08-26  06:45:00    2   1800
2019-08-26  07:00:00    2   1800
2019-08-26  07:15:00    2   1800
2019-08-26  07:30:00    2   1800
2019-08-26  07:45:00    2   1800
2019-08-26  08:00:00    2   1800
2019-08-26  08:15:00    2   1800
2019-08-26  08:30:00    2   1800
2019-08-26  08:45:00    2   1800
2019-08-26  09:00:00    2   1800
2019-08-26  09:15:00    2   1800
2019-08-26  09:30:00    2   1800
2019-08-26  09:45:00    2   1800
2019-08-26  10:00:00    2   1800
2019-08-26  10:15:00    2   1800
2019-08-26  10:30:00    2   1800
2019-08-26  10:45:00    2   1800
2019-08-26  11:00:00    2   1800
2019-08-26  11:15:00    2   1800
2019-08-26  11:30:00    2   1800
2019-08-26  11:45:00    2   1800
2019-08-26  12:00:00    2   1800
2019-08-26  12:15:00    2   1800
2019-08-26  12:30:00    2   1800
2019-08-26  12:45:00    2   1800
2019-08-26  13:00:00    2   1800
2019-08-26  13:15:00    2   1800
2019-08-26  13:30:00    2   1800
2019-08-26  13:45:00    2   1477

How to count users logged with duration per date and 15 min interval

Answers (1)

Related Questions