How to count how many userid are log-in by hour?

[ddtpmyra]

I don’t have scripts right now but maybe I’ll put my problem into words and examples of what I wanted to do.

I want to count how many user is login in every hour. This is to measure system utilization. The user’s come in and log out anytime of the day.
Table looks like this

[B]Date     UserID  Login  LogOut[/B]
2/7/2011  Cat     8:00   9:00
2/7/2011  Dog     9:12  10:30
2/7/2011  Cat    10:01  15:00
2/7/2011  Dog    10:45  17:00
2/7/2011  Cat    15:30  17:00

The query I wanted to build is to count how many UserID is currently log-in by hour from 8am to 5pm.

Select count(userid), login, date
Where ---I don’t know I’m black out----------
Group by login, date

Thanks for your help and I will definitely appreciate all your inputs

[Rabbit]

Group by the hour of the log in. You may also want the hour of the log in in the select clause.

[ADezii]

@ddtpmyra, are these the results that you are looking for?

Date	      8 to 8_59	9 to 9_59	10 to 10_59	11 to 11_59	12 to 12_59    1 to 1_59	2 to 2_59	3 to 3_59	4 to 4_59
2 /7 /2011	     1	       2	        3	          2	          2	          2	        2	        3	        2

[ddtpmyra]

Adezii,

Yes I want to count how many currently login on each hour. I wanted to capture even they log at early times like 8am and not logout since after 10, I wanted to be counted on the 10am. And there are time they will be login and logging out, I can't configure a query how to do this.

[ddtpmyra]

Hi Rabbit,

I was able to get the per hour value of each field but how can I count how many login each hour?

[ADezii]

@ddtpmyra - I do believe that I have a solution, but it is rather awkward and cumbersome. I'll Post the SQL along with the Demo Database (Attachment) that I used for this Thread, but I'm quite sure that Rabbit will come up with a much better solution.

SELECT tblLogins.Date, DCount("*","tblLogins","8 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [8 to 8_59], 
DCount("*","tblLogins","9 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [9 to 9_59], 
DCount("*","tblLogins","10 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [10 to 10_59], 
DCount("*","tblLogins","11 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [11 to 11_59], 
DCount("*","tblLogins","12 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [12 to 12_59], 
DCount("*","tblLogins","13 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [1 to 1_59], 
DCount("*","tblLogins","14 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [2 to 2_59], 
DCount("*","tblLogins","15 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [3 to 3_59], 
DCount("*","tblLogins","16 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [4 to 4_59]
FROM tblLogins
GROUP BY tblLogins.Date;

[Rabbit]

Well, if the log in time is a date/time field, I suspect you can do something like

SELECT Count(*), [Date], Hour(Login) AS HourOfLogin
FROM tblLogins
GROUP BY [Date], Hour(Login)

This puts the data in a form different from ADezii's example but you could crosstab/pivot it to achieve that layout.

[ADezii]

@Rabbit - I was thinking along the same lines as you, but this is where I got handcuffed. The Hour(Login) appears to me to be meaningless, since the following Login and Logout Span: 10:45 - 17:00 (Record #5) would not show up in the Query for the 11th, 12th, 13th, 14th, 15th, and 16th Hour(Login), but is obviously within those Hour(Login) Ranges. Am I missing something, since you know how bad my SQL is! (LOL).

[Oralloy]

Try creating a support function called IsLoggedIn(rang e, hour), which returns zero or one, depending on whether the hour lies in the range or not.

[Rabbit]

Sorry, I misunderstood what the OP was looking for.

I would create a table of hours. Basically a table with the numbers 1-24 or 0-23 if that is the case.
Then I would use

SELECT Count(*), Date, LoginHour
FROM tblLogins, tblHours
WHERE LoginHour BETWEEN Hour(Login) AND Hour(Logout)
GROUP BY Date, LoginHour

You could also do the same thing with a subquery. I don't know which would be faster. I suspect the subquery may be faster due to the limited number of records in tblHours while the crossjoin would be slower due to (possibly) more records in tblLogins.

[ddtpmyra]

Rabit and Adenzii I appreciate looking into this.

I know its a little bit tricky I actually starting to pull out my hair here :)

Adenzii query almost hit it.... it's just I dont need to count how many users are loggin in but the challenging part is to count how many currently login (most or last) and considering the their last logout as well.

[Oralloy]

Do you write a record one time only, or do you write twice - once on login, and once on logout?

If you write twice, just

SELECT UserID
  FROM Users
  WHERE LogOut IS NULL

[ADezii]

@ddtpmyra - Given the Data you supplied in Post #1, what, exactly, should the Results be? Let's reverse engineer this for now (LOL).

[ddtpmyra]

You guys are awesome. I think and as I mentioned I have to consider the last log-out. So far this query works for me but Im not sure (crossing my fingers) more testing should be done.

SELECT Count(*) AS AgentCount, tblHours.LoginHour
FROM DAgentLogin, tblHours
WHERE (((tblHours.LoginHour) Between Hour([LoginTime]) And Hour([LogoutTime])) AND ((tblHours.LoginHour)<Hour([LogoutTime])))
GROUP BY tblHours.LoginHour, DAgentLogin.EventDate
HAVING (((DAgentLogin.EventDate)=#2/1/2011#))
ORDER BY tblHours.LoginHour;

[Oralloy]

What if they log in multiple times during the day?

This is gonna sound foolish, but can people be logged in across midnight? This might be pertinent, if some one logs on in the afternoon and leaves their computer running all night.

If you can modify your login/out table to timestamp login and logout, rather than separating the date and time fields, you can run a fairly straightforward query to get each user's last login (or logout):

SELECT UserID, MAX(Login), MAX(Logout)
  FROM DAgentLogin
  GROUP By UserID

I know that this would require some application restructuring. You can also construct the Login and Logout values, if need be. Maybe something like this (although I haven't tested it):

SELECT UserID, MAX(Login), MAX(Logout)
  FROM (SELECT UserId,
               TIMESTAMP(EventDate & " " & LoginTime) Login, 
               TIMESTAMP(EventDate & " " & LogoutTime) Logout
          FROM DAgentLogin)
  GROUP By UserID