Query to count concurrent users every hour

Question

Query to count concurrent users every hour

Gstar1224

SSC Enthusiast

Points: 185
More actions
February 18, 2016 at 6:03 pm

#316047

Hey Everyone,
I have been trying to build a query that would calculate the number of concurrent users every hour for a date range(Lets say a month). Below is an example of the outputs from a log table in the DB:
RECORD_ID, Login ,Logout , User_ID
27998,1/5/2015 16:17,1/14/2015 16:25,66UHDJDE
27998,1/5/2015 16:22,1/5/2015 16:22,ZEJSDJDF
27999,1/5/2015 16:29,1/5/2015 17:31,DJF2DJED
28002,1/5/2015 17:39,1/5/2015 17:40,HE6EEBDL
28003,1/5/2015 18:05,1/5/2015 18:13,HQ2RKEJE
28003,1/5/2015 18:08,1/5/2015 19:55,3SK3NDH
28007,1/6/2015 5:39,1/6/2015 13:28,6Y0HIEHR
28007,1/6/2015 5:42,1/6/2015 6:20,ZNZFJNNS
I am trying to get an output that returns like this:
Date-Hour,Concurrent Users
1/4/2015 0:00,281
1/4/2015 1:00,281
1/4/2015 2:00,281
1/4/2015 3:00,281
1/4/2015 4:00,281
1/4/2015 5:00,285
1/4/2015 6:00,293
1/4/2015 7:00,312
1/4/2015 8:00,326
1/4/2015 9:00,335
1/4/2015 10:00,346
1/4/2015 11:00,344
1/4/2015 12:00,341
1/4/2015 13:00,350
1/4/2015 14:00,337
Any ideas on how this can be done?
Thanks for any help!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

Gstar1224 (2/18/2016)
Hey Everyone,
I have been trying to build a query that would calculate the number of concurrent users every hour for a date range(Lets say a month). Below is an example of the outputs from the log table in the DB:
RECORD_ID, Login ,Logout , User_ID
2799860,1/5/2015 16:17,1/14/2015 16:25,66UH
2799892,1/5/2015 16:22,1/5/2015 16:22,ZEJS
2799924,1/5/2015 16:29,1/5/2015 17:31,DJF2
2800229,1/5/2015 17:39,1/5/2015 17:40,HE6E
2800301,1/5/2015 18:05,1/5/2015 18:13,HQ2R
2800309,1/5/2015 18:08,1/5/2015 19:55,3SK3
2800708,1/6/2015 5:39,1/6/2015 13:28,6Y0H
2800709,1/6/2015 5:42,1/6/2015 6:20,ZNZF
2800714,1/6/2015 5:56,1/6/2015 15:17,JI66
2800717,1/6/2015 6:04,1/6/2015 15:53,J7XK
2800726,1/6/2015 6:12,1/11/2015 14:40,HF4D
2800729,1/6/2015 6:17,1/6/2015 9:16,HZek
2800732,1/6/2015 6:21,1/6/2015 12:43,ZNZF
2800746,1/6/2015 6:27,1/6/2015 8:50,GKGU
I am trying to get an output that returns like this:
Date-Hour,Concurrent Users
1/4/2015 0:00,281
1/4/2015 1:00,281
1/4/2015 2:00,281
1/4/2015 3:00,281
1/4/2015 4:00,281
1/4/2015 5:00,285
1/4/2015 6:00,293
1/4/2015 7:00,312
1/4/2015 8:00,326
1/4/2015 9:00,335
1/4/2015 10:00,346
1/4/2015 11:00,344
1/4/2015 12:00,341
1/4/2015 13:00,350
1/4/2015 14:00,337
Any ideas on how this can be done? I can build the formula in excel but i wanted to see if there is a query that could produce that same output.
Thanks for any help!

Your problem is interesting but your post is missing some critical information. Please provide a CREATE TABLE statement, INSERT statements to populate it with representative test data and your desired results based on that test data (not the case in your post).

And since you have solved this in Excel, if you wouldn't mind posting the formula(s) you used it may help us understand any business rules tucked into the logic.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 2

Here is the framework for a solution but I need some of the information I requested to be sure it meets all the business requirements:

if OBJECT_ID('tempdb.dbo.#hours') is not null

drop table #hours;

if OBJECT_ID('tempdb.dbo.#logins') is not null

drop table #logins;

declare @start datetime = '20150101', @end datetime = '20160101', @hours int;

set @hours = datediff(hour,@start,@end);

-- build a table with an hour for each day between our start and end

with cte_hours as ( select top (@hours) row_number() over (order by (select null)) as hour_time

from master.sys.all_columns c1

cross join master.sys.all_columns c2

cross join master.sys.all_columns c3)

select dateadd(hour, row_number() over (order by (select null)), @start) as hour_time

into #hours

from cte_hours;

select cast(2799860 as int) as RECORD_ID,

cast('1/5/2015 16:17' as datetime) as [Login],

cast('1/14/2015 16:25' as datetime) as Logout,

cast('66UH' as sysname) as [User_ID]

into #logins

union all select 2799892 ,'1/5/2015 16:22','1/5/2015 16:22','ZEJS'

union all select 2799924 ,'1/5/2015 16:29','1/5/2015 17:31','DJF2'

union all select 2800229 ,'1/5/2015 17:39','1/5/2015 17:40','HE6E'

union all select 2800301 ,'1/5/2015 18:05','1/5/2015 18:13','HQ2R'

union all select 2800309 ,'1/5/2015 18:08','1/5/2015 19:55','3SK3'

union all select 2800708 ,'1/6/2015 5:39','1/6/2015 13:28','6Y0H'

union all select 2800709 ,'1/6/2015 5:42','1/6/2015 6:20','ZNZF'

union all select 2800714 ,'1/6/2015 5:56','1/6/2015 15:17','JI66'

union all select 2800717 ,'1/6/2015 6:04','1/6/2015 15:53','J7XK'

union all select 2800726 ,'1/6/2015 6:12','1/11/2015 14:40','HF4D'--

union all select 2800729 ,'1/6/2015 6:17','1/6/2015 9:16','HZek'

union all select 2800732 ,'1/6/2015 6:21','1/6/2015 12:43','ZNZF'

union all select 2800746 ,'1/6/2015 6:27','1/6/2015 8:50','GKGU';

select h.hour_time, count(*) as concurrent_logins

--, dbo.GROUP_CONCAT(RECORD_ID) as Records -- show all the RECORD_IDs in the group (http://groupconcat.codeplex.com)

from #hours h

cross join #logins l

where h.hour_time between l.[Login] and l.Logout

group by h.hour_time

order by h.hour_time;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004600 More actions · Answer 3

Gstar1224 (2/18/2016)
...calculate the number of concurrent users every hour...

What does that actually mean? Do you mean, for example, if someone were logged in from 1:30AM until 4:30AM, they should be included in the total for each of the 1, 2, 3, and 4 AM hour periods?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

Jeff Moden (2/18/2016)
Gstar1224 (2/18/2016)
...calculate the number of concurrent users every hour...
What does that actually mean? Do you mean, for example, if someone were logged in from 1:30AM until 4:30AM, they should be included in the total for each of the 1, 2, 3, and 4 AM hour periods?

Exactly. Is it point in time concurrent at the top of every hour is it any time within that hour block (not necessarily concurrent, more users per hour).

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Query to count concurrent users every hour

Cookies on SQLServerCentral