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

Date     UserID  Login  LogOut
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(range, 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

[Oralloy]

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.

[ADezii]

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...

[ADezii]

@ddtpmyra - Kindly respond to Post #13, so we all know exactly the Results we should be looking for.

[Oralloy]

@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.

[ADezii]

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

[ddtpmyra]

@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!

[ddtpmyra]

actually 17 should be disregard it should be zero or null because everybodys are out at 17hours

[NeoPa]

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.

[NeoPa]

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?

[ddtpmyra]

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.

[ddtpmyra]

Adezii put a lot of ideas too, I wish I can vote the best of two. Thank you all!

[NeoPa]

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.

[Oralloy]

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....

[ADezii]

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.

[NeoPa]

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]

Hour (Date/Time)
 8:00
 9:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00

[tblLog]

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

SELECT   Hour(tH.Hour) AS [Hours]
       , Count(tL.UserID) AS [LoggedOn]
FROM     [tblHour] AS tH
         LEFT JOIN
         [tblLog] AS tL
  ON     (tH.Hour<=TimeValue(Nz(tL.LogOut,#23:59#)))
 AND     (DateAdd('h',1,tH.Hour)>TimeValue(tL.LogIn))
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).

[ADezii]

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.

[NeoPa]

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.

[NeoPa]

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.

[ADezii]

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

'SQL Statement by NeoPa with GROUP BY Clause added
SELECT Hour(tH.Hour) AS Hours, Count(tL.UserID) AS LoggedOn
FROM tblHour AS tH LEFT JOIN tblLogins AS tL ON (DateAdd('h',1,tH.Hour)>TimeValue(tL.LogIn)) 
AND (tH.Hour<=TimeValue(Nz(tL.LogOut,#12/30/1899 23:59:0#)))
GROUP BY Hour(tH.Hour);

Hour       OP           NeoPa
 8:00      1              1
 9:00      1      <>      2
10:00      2      <>      3
11:00      2              2
12:00      2              2
13:00      2              2
14:00      2              2
15:00      2      <>      3
16:00      2              2
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.

[NeoPa]

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.

[ddtpmyra]

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?

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]

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.

[NeoPa]

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.

SELECT   [Hours]
       , Count(*) AS [LoggedOn]
FROM     (
    SELECT DISTINCT
             tL.UserID
           , Hour(tH.Hour) AS [Hours]
    FROM     [tblHour] AS tH
             LEFT JOIN
             [tblLog] AS tL
      ON     (tH.Hour<=TimeValue(Nz(tL.LogOut,#23:59#)))
     AND     (DateAdd('h',1,tH.Hour)>TimeValue(tL.LogIn))
         ) AS sQ
GROUP BY [Hours]

This gives the results as required of :

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

[ADezii]

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.

[NeoPa]

Look at the data I had to change in my earlier post (#30) to be reflective of what the OP meant to ask.