February 18, 2016 at 6:03 pm
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!
February 18, 2016 at 6:51 pm
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
February 18, 2016 at 6:57 pm
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
February 18, 2016 at 8:32 pm
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
Change is inevitable... Change for the better is not.
February 18, 2016 at 9:05 pm
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy