Finding total and Concurrent number of users logged in

[krvrk]

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

[krvrk]

any ideas or code please!!!!!!

[Motoma]

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

The total should just be:

SELECT
  COUNT(*)
FROM
  Table
WHERE
  SessionStart BETWEEN <date1> AND <date2>
    OR
  SessionEnd BETWEEN <date1> AND <date2>
 

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

[krvrk]

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

[Motoma]

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:

-------                             Time
User 1  ------------------------------------------------------------
User 2  --------------------                             ----------
User 3  -----------                             --------------------
User 4                 ------------------           
 

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"?

[krvrk]

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.

-------        
---Time       8       9      10   11    12    13     14
User 1  ------------------------------------------------------------
User 2  --------------------                      ------------------
User 3  -----------                               ------------------
User 4                   ------------------        
User 5            --------------------------------- 
User 6            --------
User 7            ------
User 8                                          ---------------------
User 9                                         ----------------------
User 10                 --------------------------------------------             
 
 

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

[mihir2508]

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