CODEWITHSUNDEEP
sqlsql-serverdatetime
Karthick Raju
Karthick Raju

Reputation: 797

Group by data to get count between the datetime range

Let's say I've a table like below 

start_time           end_time             user_name
2019-01-01 00:00:05  2019-01-01 00:05:05  user1
2019-01-01 00:01:35  2019-01-01 00:06:05  user2
2019-01-01 00:02:05  2019-01-01 00:07:05  user3
2019-01-01 00:03:05  2019-01-01 00:08:05  user1
2019-01-01 00:04:05  2019-01-01 00:09:05  user2

My objective is find out how many users were logged in for a MINUTE. Say like below

time                  active no of users
2019-01-01 00:00:00   1
2019-01-01 00:01:00   2
2019-01-01 00:02:00   3
2019-01-01 00:03:00   3
2019-01-01 00:04:00   3

Now I first tried to round of time for a new column dateadd(mi, datediff(mi, 0, dateadd(s, 30, start_time)), 0). So, I will receive like above table time column

Next I tried to find the count for rounded datetime like below

SELECT
dateadd(mi, datediff(mi, 0, dateadd(s, 30, start_time)), 0) as RoundedDateTime,
(
    SELECT  count(distinct(user_name)) 
    FROM entrytable sh
    WHERE (sh.end_time > dateadd(mi, datediff(mi, 0, dateadd(s, 30, t.start_time)), 0) 
    and sh.start_time <= dateadd(mi, datediff(mi, 0, dateadd(s, 30, t.start_time)), 0))        
) as usercounter
FROM entrytable t

But, above SQL query is running for longer time and goes to not responding mode.

I could not fix the issue. Can someone help?

Thanks in advance!

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This question was originally tagged for SQL Server 2012, so this answer is for SQL Server.

One method is to generate a list of minutes and then:

with minutes as (
      select cast('2019-01-01 00:00:00' as datetime) as mm
      union all
      select dateadd(minute, 1, minute)
      from cte
      where mm < '2019-01-01 00:00:05'
     )
select m.*,
       (select count(*)
        from entrytable et
        where et.start_time <= m.mm and
              et.end_time > m.mm
       ) as num_actives
from minutes m;

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

The most trivial solution is this:

DECLARE @t TABLE (start_time datetime, end_time datetime, user_name varchar(10));
INSERT INTO @t VALUES
('2019-01-01 00:00:05', '2019-01-01 00:05:05', 'user1'),
('2019-01-01 00:01:35', '2019-01-01 00:06:05', 'user2'),
('2019-01-01 00:02:05', '2019-01-01 00:07:05', 'user3'),
('2019-01-01 00:03:05', '2019-01-01 00:08:05', 'user1'),
('2019-01-01 00:04:05', '2019-01-01 00:09:05', 'user2');

SELECT dt AS date_time, SUM(SUM(val)) OVER (ORDER BY dt) AS active_count
FROM (
    SELECT start_time, +1 FROM @t UNION ALL
    SELECT end_time,   -1 FROM @t
) cte1(dt, val)
GROUP BY dt

This will give you the number of active users whenever there was a change (someone logged in or logged out). Result:

| date_time               | active_count |
|-------------------------|--------------|
| 2019-01-01 00:00:05.000 | 1            |
| 2019-01-01 00:01:35.000 | 2            |
| 2019-01-01 00:02:05.000 | 3            |
| 2019-01-01 00:03:05.000 | 4            |
| 2019-01-01 00:04:05.000 | 5            |
| 2019-01-01 00:05:05.000 | 4            |
| 2019-01-01 00:06:05.000 | 3            |
| 2019-01-01 00:07:05.000 | 2            |
| 2019-01-01 00:08:05.000 | 1            |
| 2019-01-01 00:09:05.000 | 0            |

Be advised that the result does not contain the "in-between" dates.

Upvotes: 1

Related Questions