469,360 Members | 1,807 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

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

ddtpmyra
333 100+
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
Expand|Select|Wrap|Line Numbers
  1. Date     UserID  Login  LogOut
  2. 2/7/2011  Cat     8:00   9:00
  3. 2/7/2011  Dog     9:12  10:30
  4. 2/7/2011  Cat    10:01  15:00
  5. 2/7/2011  Dog    10:45  17:00
  6. 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.

Expand|Select|Wrap|Line Numbers
  1. Select count(userid), login, date
  2. Where ---I don’t know I’m black out----------
  3. Group by login, date
Thanks for your help and I will definitely appreciate all your inputs
Feb 7 '11 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*), Date, LoginHour
  2. FROM tblLogins, tblHours
  3. WHERE LoginHour BETWEEN Hour(Login) AND Hour(Logout)
  4. 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.

39 4637
Rabbit
12,516 Expert Mod 8TB
Group by the hour of the log in. You may also want the hour of the log in in the select clause.
Feb 7 '11 #2
ADezii
8,800 Expert 8TB
@ddtpmyra, are these the results that you are looking for?
Expand|Select|Wrap|Line Numbers
  1. 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. 2 /7 /2011         1           2            3              2              2              2            2            3            2
Feb 8 '11 #3
ddtpmyra
333 100+
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.
Feb 8 '11 #4
ddtpmyra
333 100+
Hi Rabbit,

I was able to get the per hour value of each field but how can I count how many login each hour?
Feb 8 '11 #5
ADezii
8,800 Expert 8TB
@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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblLogins.Date, DCount("*","tblLogins","8 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [8 to 8_59], 
  2. DCount("*","tblLogins","9 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [9 to 9_59], 
  3. DCount("*","tblLogins","10 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [10 to 10_59], 
  4. DCount("*","tblLogins","11 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [11 to 11_59], 
  5. DCount("*","tblLogins","12 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [12 to 12_59], 
  6. DCount("*","tblLogins","13 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [1 to 1_59], 
  7. DCount("*","tblLogins","14 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [2 to 2_59], 
  8. DCount("*","tblLogins","15 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [3 to 3_59], 
  9. DCount("*","tblLogins","16 Between Hour(tblLogins.Login) And Hour(tblLogins.Logout)") AS [4 to 4_59]
  10. FROM tblLogins
  11. GROUP BY tblLogins.Date;
Attached Files
File Type: zip Logins By Hour_2.zip (16.8 KB, 135 views)
Feb 8 '11 #6
Rabbit
12,516 Expert Mod 8TB
Well, if the log in time is a date/time field, I suspect you can do something like
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*), [Date], Hour(Login) AS HourOfLogin
  2. FROM tblLogins
  3. 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.
Feb 8 '11 #7
ADezii
8,800 Expert 8TB
@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).
Feb 8 '11 #8
Oralloy
983 Expert 512MB
Try creating a support function called IsLoggedIn(range, hour), which returns zero or one, depending on whether the hour lies in the range or not.
Feb 8 '11 #9
Rabbit
12,516 Expert Mod 8TB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*), Date, LoginHour
  2. FROM tblLogins, tblHours
  3. WHERE LoginHour BETWEEN Hour(Login) AND Hour(Logout)
  4. 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.
Feb 8 '11 #10
ddtpmyra
333 100+
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.
Feb 8 '11 #11
Oralloy
983 Expert 512MB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT UserID
  2.   FROM Users
  3.   WHERE LogOut IS NULL
Feb 8 '11 #12
ADezii
8,800 Expert 8TB
@ddtpmyra - Given the Data you supplied in Post #1, what, exactly, should the Results be? Let's reverse engineer this for now (LOL).
Feb 8 '11 #13
ddtpmyra
333 100+
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS AgentCount, tblHours.LoginHour
  2. FROM DAgentLogin, tblHours
  3. WHERE (((tblHours.LoginHour) Between Hour([LoginTime]) And Hour([LogoutTime])) AND ((tblHours.LoginHour)<Hour([LogoutTime])))
  4. GROUP BY tblHours.LoginHour, DAgentLogin.EventDate
  5. HAVING (((DAgentLogin.EventDate)=#2/1/2011#))
  6. ORDER BY tblHours.LoginHour;
  7.  
Feb 8 '11 #14
Oralloy
983 Expert 512MB
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):
Expand|Select|Wrap|Line Numbers
  1. SELECT UserID, MAX(Login), MAX(Logout)
  2.   FROM DAgentLogin
  3.   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):
Expand|Select|Wrap|Line Numbers
  1. SELECT UserID, MAX(Login), MAX(Logout)
  2.   FROM (SELECT UserId,
  3.                TIMESTAMP(EventDate & " " & LoginTime) Login, 
  4.                TIMESTAMP(EventDate & " " & LogoutTime) Logout
  5.           FROM DAgentLogin)
  6.   GROUP By UserID
Feb 8 '11 #15
Oralloy
983 Expert 512MB
Actually, I don't know if you can run the aggrigation functions over the TIMESTAMPS, if so, then you won't need the nested query.
Feb 8 '11 #16
ADezii
8,800 Expert 8TB
The query I wanted to build is to count how many UserID is currently log-in by hour from 8am to 5pm.
@Oralloy - The relevant Time Period, as stated in post #1 is 8:00 A.M. to 5:00 P.M. Nevermind, misread as usual...
Feb 8 '11 #17
ADezii
8,800 Expert 8TB
@ddtpmyra - Kindly respond to Post #13, so we all know exactly the Results we should be looking for.
Feb 8 '11 #18
Oralloy
983 Expert 512MB
@ADezii - I thought that ddtpmyra was trying to filter based on date in his posted query. What I was attempting to do was provide a query giving the last login and logout for each user. This information could then be used with the query of hours to give the result he's looking for. After all, a user that hasn't logged in for three days will still show up in his counts, if he doesn't do something about the day.
Feb 8 '11 #19
ADezii
8,800 Expert 8TB
@Oralloy - I think at this time we have to all 'take a back seat' until we know exactly what Results the Op is looking for. This is why I have been requesting clarification on these Results given the initial Data Posted (Post #1). I thought the correct Results were returned in Post #6, but as indicated by the OP (Post #11), this was 'close', but not dead on. Interesting Thread, heh?
Feb 8 '11 #20
ddtpmyra
333 100+
@Adezii here's my expected results:

DATA
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

Hours #currently_login
8 1
9 1
10 2
11 2
12 2
13 2
14 2
15 2
16 2
17 2

@Oralloy: Please disregard the date filtered.

Thanks!
Feb 8 '11 #21
ddtpmyra
333 100+
actually 17 should be disregard it should be zero or null because everybodys are out at 17hours
Feb 8 '11 #22
NeoPa
32,185 Expert Mod 16PB
ddtpmyra:
@Oralloy: Please disregard the date filtered.
It seems to me that this is exactly where you have the design wrong. You are trying to leave dates out of the equation, and treating them as separate, which of course they cannot be.

I would suggest you give more thought to what Oralloy has posted, as it seems to me he has the best appreciation of what is required (with no disrespect to anyone else involved). You should be looking to store a Date/Time value for each of the login and the logout times. Not just a time. That is just adding complexity to your task.

When you have that sorted sensibly, you are then looking at the concept of overlapping date/times. This is often misunderstood, but essentially if you have Period 1 and Period 2 to compare and determine if there is any overlap (in your example you could consider Period 1 to represent the working hours in a particular day and Period 2 to represent, separately for each log record, the login and logout times), then you need to check that the start of Period 1 is less than the end of Period 2 and that the end of Period 1 is greater than the start of Period 2. This is not as simple as a Between check in your WHERE clause, but once you get the concept you'll find that it works.

Ultimately, it is possible to do this storing the dates and times separately, but it is not recommended, and is much more complex. Your choice of course, but I can't offer to travel with you on that road.
Feb 8 '11 #23
NeoPa
32,185 Expert Mod 16PB
Also, I can't find it on a quick scan, but someone mentioned the possibility of running this when there are still people logged onto the system. It seems this must be considered and your example data doesn't include any such records, which is a problem.

The solution is relatively simple though (luckily). You simply use Nz() to refer to whichever field you have which may hold no value yet (is Null), and pass a default value of a date/time in the long-distant future. IE. Replace every instance of [LogOut] with Nz([LogOut],#12/31/9999#). Does that make sense?
Feb 8 '11 #24
ddtpmyra
333 100+
NeoPa... thanks for the inputs the dates are definitely important it's just i want to make it simplier for everybody who will look into it, that's why I only use timestamp.
Feb 9 '11 #25
ddtpmyra
333 100+
Adezii put a lot of ideas too, I wish I can vote the best of two. Thank you all!
Feb 9 '11 #26
NeoPa
32,185 Expert Mod 16PB
ddtpmyra:
it's just i want to make it simplier for everybody who will look into it, that's why I only use timestamp.
And you think separating the date and time elements will do that for you? I'm sorry but you will not find that to be the case.

Displaying them simply with a time format may help, but storing them separately won't. I suppose you'll have to find that out for yourself though. It's absolutely your choice how you choose to handle your own issues and questions, so I can only wish you good luck.
Feb 9 '11 #27
Oralloy
983 Expert 512MB
Folks, if I may come back into the conversation.....

My personal experience with date/time issues is that every database implementation which separates the two ends up with long-term problems and extra code.

This has been realized by the industry, which has implemented the idea of the time-difference.

So, we have timestamp type values represent absolute times, and time-differenc types that represent relative times or repeating times.

ddtpmyra's problem requires both. Users log in and out at absolute times, but his report is based on times relative to a specific day. That day may be "today", but the times (8am, 9am, etc) are relative to the day.

Which leads me to ask the following questions:
  1. Is the login/out table [DAgentLogin] structure fixed, or is it still mutable?
  2. How do you handle disconnects that do not write a logout record? Is the table written only on logout, or is it INSERTed at login and UPDATEd at logout?
  3. Do you want a report spanning multiple days, or just "today"?

Essentially, what are the overarching requirements for this user summary report? In this case, we need to know before we can advise you in any more depth on query design.

Ok, I'll shut up now....
Feb 9 '11 #28
ADezii
8,800 Expert 8TB
Out of curiosity, has anyone actually created the SQL that produces the desired Results as indicated in Post #21? I personally would love to see it.
Feb 9 '11 #29
NeoPa
32,185 Expert Mod 16PB
Before we start, I should say I had to adjust the data to get the figures to match it. Logging out at 09:00 should actually reflect hours of both 08:00 and 09:00 according to the OP's specification. I also adjusted the data so that Dog hasn't signed out yet after logging in at 10:45.

[tblHour]
Expand|Select|Wrap|Line Numbers
  1. Hour (Date/Time)
  2.  8:00
  3.  9:00
  4. 10:00
  5. 11:00
  6. 12:00
  7. 13:00
  8. 14:00
  9. 15:00
  10. 16:00
  11. 17:00
[tblLog]
Expand|Select|Wrap|Line Numbers
  1. Date     UserID  Login  LogOut
  2. 2/7/2011  Cat     8:00   8:59
  3. 2/7/2011  Dog     9:12  10:30
  4. 2/7/2011  Cat    10:01  15:00
  5. 2/7/2011  Dog    10:45
  6. 2/7/2011  Cat    15:30  17:00
Expand|Select|Wrap|Line Numbers
  1. SELECT   Hour(tH.Hour) AS [Hours]
  2.        , Count(tL.UserID) AS [LoggedOn]
  3. FROM     [tblHour] AS tH
  4.          LEFT JOIN
  5.          [tblLog] AS tL
  6.   ON     (tH.Hour<=TimeValue(Nz(tL.LogOut,#23:59#)))
  7.  AND     (DateAdd('h',1,tH.Hour)>TimeValue(tL.LogIn))
  8. GROUP BY tH.Hour
No benefit from storing only time values, but I would expect this to provide the results as specified. I'm sure you'll let me know otherwise if I'm wrong ADezii (He has before when I've slipped up :-D).
Feb 9 '11 #30
ADezii
8,800 Expert 8TB
Close, NeoPa, but no cigar as of yet ==> Query does not include the Expression Hour(tH.Hour) as part of an Aggregate Function. Personally, I'm still baffled.
Feb 9 '11 #31
NeoPa
32,185 Expert Mod 16PB
You beat me to it ADezii. I thought of the missing GROUP BY clause after I'd gone to bed last night (Sad I know :-D). I will update the original post to avoid loads of different posts in the thread with partial solutions.
Feb 9 '11 #32
NeoPa
32,185 Expert Mod 16PB
To explain the error message (which to be fair is one that many many people struggle with) :

Queries can either run to produce a result record for each set of data from the input pool, or they can group some (or all) of the data from the input pool together and produce a resultant record from that set.

In the former case aggregate functions are not allowed. Count(*) makes little sense when dealing with a group of only a single record.

In the latter case you are dealing with a group of records (which may be only a single record in some cases, but the concept is to handle multiple records). In this situation there are two fundamental ways of returning data that make any sense :
  1. Aggregate functions (Sum(); Count(); Max(); First(); etc) return a single value that covers all the input values. Similar in effect to the Domain Aggregate functions you will be familiar with (DSum(); DCount(); DMax(); etc). The value returned depends on the specific aggregate function called, but essentially they all aggregate the data.
  2. The input data itself. The important thing to remember about dealing with the data itself directly, from within a GROUP BY query, is that only items that are specifically included in the GROUP BY clause itself can be selected that way. This is because when the data is GROUPed BY a particular field you are specifically indicating that, no matter how many input records are included in the group, each record will have the same value for that field. Thus it makes sense to refer to any such field directly, whereas referring to any other field from the input recordset would necessarily be ambiguous.

I hope that helps to clarify the situation.

PS. There is an exception to the rule that all such aggregate queries need a GROUP BY clause. When aggregating across all the data as a single group, this can be unnecessary. This is not the case in my suggestion though. That was simply a careless oversight on my part.
Feb 9 '11 #33
ADezii
8,800 Expert 8TB
@NeoPa - Hope you don't mind NeoPa, but I took the liberty of adding the GROUP BY Clause to your Posted SQL Statement, and found two very interesting items:
  1. The Results your SQL achieved are exactly the same as my Results as Posted in Post #6, except that I considered the 15:00 Hour irrelevant. Of course, my approach was rather primitive and ugly, but I did think that the Logic was sound.
  2. These latest Results are still not what the OP is expecting as indicated in Post #21, and displayed below (Records 2, 3, and 8). Back to the Drawing Board?
Expand|Select|Wrap|Line Numbers
  1. 'SQL Statement by NeoPa with GROUP BY Clause added
  2. SELECT Hour(tH.Hour) AS Hours, Count(tL.UserID) AS LoggedOn
  3. FROM tblHour AS tH LEFT JOIN tblLogins AS tL ON (DateAdd('h',1,tH.Hour)>TimeValue(tL.LogIn)) 
  4. AND (tH.Hour<=TimeValue(Nz(tL.LogOut,#12/30/1899 23:59:0#)))
  5. GROUP BY Hour(tH.Hour);
Expand|Select|Wrap|Line Numbers
  1. Hour       OP           NeoPa
  2.  8:00      1              1
  3.  9:00      1      <>      2
  4. 10:00      2      <>      3
  5. 11:00      2              2
  6. 12:00      2              2
  7. 13:00      2              2
  8. 14:00      2              2
  9. 15:00      2      <>      3
  10. 16:00      2              2
  11. 17:00      2              2
P.S. - From what I can see, your Results are spot on, unles I am misinterpreting something.

@ddtpmyra - Perhaps if you explained how, exactly, you arrived at the Expected Results as listed in Post #21, some light would be shed on this Topic. Thanks.
Feb 9 '11 #34
NeoPa
32,185 Expert Mod 16PB
I need to look further into this. The question, when asked fully and correctly, is not as straightforward as we've beenb led to believe. I will give this some further consideration and come back. At first glance though, I feel sure my SQL (even the version I amended mayself) is not perfect for what I suspect is the full requirement.
Feb 9 '11 #35
ddtpmyra
333 100+
The only thing im having problem with this query result... it that this count same userid multiple times. This happens when the userid log more than one in an hour. What I wanted is to count only one time. Any idea how to simplify this query to get the max login?

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS AgentCount, tblHours.LoginHour 
  2. FROM DAgentLogin, tblHours 
  3. WHERE (((tblHours.LoginHour) Between Hour([LoginTime]) And Hour([LogoutTime])) AND ((tblHours.LoginHour)<Hour([LogoutTime]))) 
  4. GROUP BY tblHours.LoginHour, DAgentLogin.EventDate 
  5. HAVING (((DAgentLogin.EventDate)=#2/1/2011#)) 
  6. ORDER BY tblHours.LoginHour; 
  7.  
Feb 10 '11 #36
Oralloy
983 Expert 512MB
Try using COUNT(DISTINCT UserID).

I know you're using VBA and Access or MSSql, but....here is a mySQL reference that may help some.
Feb 10 '11 #37
NeoPa
32,185 Expert Mod 16PB
You're certainly on the right lines there Oralloy, but MS Access Jet SQL doesn't support that construct. A subquery is required in this case I feel.

Expand|Select|Wrap|Line Numbers
  1. SELECT   [Hours]
  2.        , Count(*) AS [LoggedOn]
  3. FROM     (
  4.     SELECT DISTINCT
  5.              tL.UserID
  6.            , Hour(tH.Hour) AS [Hours]
  7.     FROM     [tblHour] AS tH
  8.              LEFT JOIN
  9.              [tblLog] AS tL
  10.       ON     (tH.Hour<=TimeValue(Nz(tL.LogOut,#23:59#)))
  11.      AND     (DateAdd('h',1,tH.Hour)>TimeValue(tL.LogIn))
  12.          ) AS sQ
  13. GROUP BY [Hours]
This gives the results as required of :
Expand|Select|Wrap|Line Numbers
  1. Hours  LoggedOn
  2.    8      1
  3.    9      1
  4.   10      2
  5.   11      2
  6.   12      2
  7.   13      2
  8.   14      2
  9.   15      2
  10.   16      2
  11.   17      2
Feb 11 '11 #38
ADezii
8,800 Expert 8TB
Finally, a Resolution! Nice job NeoPa. Now all I have to do is to figure why I am getting a Value of 2 for LoggedOn at 9:00 A.M., and I'll be happy.
Feb 11 '11 #39
NeoPa
32,185 Expert Mod 16PB
Look at the data I had to change in my earlier post (#30) to be reflective of what the OP meant to ask.
Feb 12 '11 #40

Post your reply

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

Similar topics

1 post views Thread by FatBoyThin | last post: by
2 posts views Thread by Erik Lautier | last post: by
5 posts views Thread by FFMG | last post: by
1 post views Thread by lilly07 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.