By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,042 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,042 IT Pros & Developers. It's quick & easy.

Finding total and Concurrent number of users logged in

P: 4
Hi,

I am creating a report for which i need to query a table for total number of users and Concurrent users logged in.

The table will contain Username(nvarcar,not null),SessionStart time(datetime, not null), SessionEnd time (datetime,null), SessionID(int,not null) and ConnectGUID(uniqueidentifier, not null) etc

can any one help me in finding total and number of concurrent logins per day.

Sample Data


Username --- Starttime --- Endtime --- SessionID

FW8126 --- 26/06/2007 00:36:34 --- 26/06/2007 01:43:42 --- 301821503
P1049 --- 26/06/2007 00:43:43 --- 26/06/2007 02:33:32 --- 1056070312
P1088 --- 26/06/2007 00:47:41 --- 26/06/2007 09:51:29 --- 312634269
R0744 --- 26/06/2007 00:47:44 --- 26/06/2007 09:46:13 --- 1068848038
P1055 --- 26/06/2007 01:03:40 --- 26/06/2007 09:54:44 --- 682893510
B0068 --- 26/06/2007 01:08:28 --- 26/06/2007 09:44:26 --- 1905880435
L0195 --- 26/06/2007 01:10:17 --- 26/06/2007 11:01:45 --- 1409172428
L0224 --- 26/06/2007 01:15:49 --- 26/06/2007 12:21:39 --- 168963861

Thanks in advance.
krvrk
Jun 28 '07 #1
Share this Question
Share on Google+
6 Replies


P: 4
any ideas or code please!!!!!!
Jun 29 '07 #2

Motoma
Expert 2.5K+
P: 3,235
Could you elaborate on what you mean by concurrent logins per day?

The total should just be:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   COUNT(*)
  3. FROM
  4.   Table
  5. WHERE
  6.   SessionStart BETWEEN <date1> AND <date2>
  7.     OR
  8.   SessionEnd BETWEEN <date1> AND <date2>
  9.  
Hi,

I am creating a report for which i need to query a table for total number of users and Concurrent users logged in.

The table will contain Username(nvarcar,not null),SessionStart time(datetime, not null), SessionEnd time (datetime,null), SessionID(int,not null) and ConnectGUID(uniqueidentifier, not null) etc

can any one help me in finding total and number of concurrent logins per day.

Sample Data


Username --- Starttime --- Endtime --- SessionID

FW8126 --- 26/06/2007 00:36:34 --- 26/06/2007 01:43:42 --- 301821503
P1049 --- 26/06/2007 00:43:43 --- 26/06/2007 02:33:32 --- 1056070312
P1088 --- 26/06/2007 00:47:41 --- 26/06/2007 09:51:29 --- 312634269
R0744 --- 26/06/2007 00:47:44 --- 26/06/2007 09:46:13 --- 1068848038
P1055 --- 26/06/2007 01:03:40 --- 26/06/2007 09:54:44 --- 682893510
B0068 --- 26/06/2007 01:08:28 --- 26/06/2007 09:44:26 --- 1905880435
L0195 --- 26/06/2007 01:10:17 --- 26/06/2007 11:01:45 --- 1409172428
L0224 --- 26/06/2007 01:15:49 --- 26/06/2007 12:21:39 --- 168963861

Thanks in advance.
krvrk
Jun 29 '07 #3

P: 4
Thanks for your reply.

Could you elaborate on what you mean by concurrent logins per day?

Concurrent logins per day means, total no of Simultaneous sessions per day.

Thanks in advance.
krvrk
Jul 2 '07 #4

Motoma
Expert 2.5K+
P: 3,235
Concurrent logins per day means, total no of Simultaneous sessions per day.
You have still been ambiguous in your explanation...
Do you mean the max number of people logged on at one time?
Or just the number of people who logged in?

Let me give you an example of why I think you need to refine your thinking of what you are looking for:

Expand|Select|Wrap|Line Numbers
  1. -------                             Time
  2. User 1  ------------------------------------------------------------
  3. User 2  --------------------                             ----------
  4. User 3  -----------                             --------------------
  5. User 4                 ------------------           
  6.  
In the above (poorly designed) graph, you have 4 users logging in over the course of the day. What would be the number you are looking for for "total simultaneous users"?
Jul 2 '07 #5

P: 4
Hi Motoma,

Sorry for confusing you.

Actually i have been told to calculate concurrent users day.
There will be around 200 logins per day, in that i need to calculate concurrent logins per day.

I don't have any idea that which is best way to calculate concurrent logins per day (i.e claculating hourly concurrent logins per day and then averaging or calculating Max no of concurrent logins in any hour.)

So, Please suggest me best way and code.

Expand|Select|Wrap|Line Numbers
  1. -------        
  2. ---Time       8       9      10   11    12    13     14
  3. User 1  ------------------------------------------------------------
  4. User 2  --------------------                      ------------------
  5. User 3  -----------                               ------------------
  6. User 4                   ------------------        
  7. User 5            --------------------------------- 
  8. User 6            --------
  9. User 7            ------
  10. User 8                                          ---------------------
  11. User 9                                         ----------------------
  12. User 10                 --------------------------------------------             
  13.  
  14.  
In the above example, At 8 No of concurrent users are 3, at 9 concurrent users are 5 and at 14 no of concurrent users are 6.

from this i want Average / Max concurrent(simultaneous) users loged for a single day.

I hope this (modified your example) will give clear idea.

Thanks for your replies.

krvrk
Jul 2 '07 #6

P: 1
CREATE TABLE Sessions
(key_col INT NOT NULL IDENTITY PRIMARY KEY,
Hour_Min VARCHAR(10) NOT NULL,
usr VARCHAR(50) NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
CHECK(endtime > starttime))

the Hour_Min is important, if you want max concurrent by hour then insert only the Hour part of the Session starttime in Hour_Min column. For eg, 10, 11, 12, 13, 15 (hours). You will get Max concurrent user for that hour.

if you want max concurrent by minute for eg. for 11:40 , 11:42 and so on, put the Hour:Minute part of the session starttime in Hour_Min column


SELECT Hour_Min, MAX(concurrent) AS mx
FROM (SELECT S1.Hour_Min, S1.key_col, COUNT(*) AS concurrent
FROM Sessions AS S1
JOIN Sessions AS S2
ON S1.Hour_Min = S2.Hour_Min
AND S1.starttime >= S2.starttime
AND S1.starttime < S2.endtime
GROUP BY S1.Hour_Min, S1.key_col) AS C
GROUP BY Hour_Mi
Aug 28 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.