Solved Determine time of activity. (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:34
Joined
Apr 27, 2015
Messages
6,286
Good afternoon esteemed AWF members!

Below is a screen shot of a table in a DB I am working with. What I would like to do is dertermin how long a given member was logged into the Application.

I've tried a few queries but I THINK the best way to do this would be something to the effect of using a Recordset that sorts on UserName and then Timestamp, finds the first Logon, marks the time and then finds the next Logoff after that time. A RBAR Sub. Any ideas?

1629819551230.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:34
Joined
Oct 29, 2018
Messages
21,358
Nah, this should be possible using queries. Are you looking for the total time for the day or each individual session?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:34
Joined
Apr 27, 2015
Messages
6,286
Individual session...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:34
Joined
Oct 29, 2018
Messages
21,358
I thought so too, but I ran out of ideas quick. The cartesian joins could not be measured with modern technology!
Right or wrong, this is why I prefer to have two separate columns for LogIn and LogOut instead of separate records.

Are you able to post a small sample db (or Excel) with test data, so we can run some SQL queries against it?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:34
Joined
Apr 27, 2015
Messages
6,286
Right or wrong, this is why I prefer to have two separate columns for LogIn and LogOut instead of separate records.
Agreed, and I may convert this table to something like that but then I would still need this query/sub to make it.
Are you able to post a small sample db (or Excel) with test data, so we can run some SQL queries against it?
Sure thing. Give me a second to port it over from the source. Appreciate you taking a look!
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:34
Joined
Sep 21, 2011
Messages
14,047
I was thinking a Dmin() to get the logoff time and have that as a separate field in a query, then subtract one from the other.?
I bit like a running sum query?

Query only for logon records and that Dmin() for the respective logoff timestamp.?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:34
Joined
Oct 29, 2018
Messages
21,358
Agreed, and I may convert this table to something like that but then I would still need this query/sub to make it.

Sure thing. Give me a second to port it over from the source. Appreciate you taking a look!
Hi. I did a quick test, and here's the result.

1629822059623.png


1629822085132.png


SQL:
SELECT tblLogs.User, tblLogs.[LogDate] AS Login,
  (SELECT Min(T1.LogDate) FROM tblLogs T1
    WHERE T1.LogAction="logout" AND T1.User=tblLogs.[User] AND T1.LogDate>=tblLogs.[LogDate]) AS Logout,
  DateDiff("n",[Login],[Logout]) AS Duration
FROM tblLogs
WHERE (((tblLogs.LogAction)="login"));
Hope that helps...
 

isladogs

MVP / VIP
Local time
Today, 21:34
Joined
Jan 14, 2017
Messages
18,186
This is also one of the few places I deliberately flout normalisation by having two fields for login time & logout time.
As @theDBguy has shown, it is possible to get around issues with only using a single field together with an identifier.
However, problems still occur where a user doesn't logout due to e.g. an illegal shut down or a power failure.
This leaves an orphan login which can cause misleading values for session duration
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:34
Joined
Apr 27, 2015
Messages
6,286
Here is the actual data. Takes a LONG time to execute!
 

Attachments

  • t_9ActivityLog1.zip
    2.1 MB · Views: 270

theDBguy

I’m here to help
Staff member
Local time
Today, 14:34
Joined
Oct 29, 2018
Messages
21,358
Here is the actual data. Takes a LONG time to execute!
Unfortunately, that's the side effect of using correlated subqueries. As @isladogs said, denormalizing this table (i.e. separate login and logout fields/columns) would speed it up a bit.
 

isladogs

MVP / VIP
Local time
Today, 21:34
Joined
Jan 14, 2017
Messages
18,186
I haven't looked at your data but as you have the same issue with 'non logouts', I would definitely restructure your table.
In my opinion, that would speed up the duration calculation a lot as the query needed to do so would be much simpler
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:34
Joined
Apr 27, 2015
Messages
6,286
My plan exactly. I will use this query to make a proper table and then go forth and sin no more (within the limits of this subject at least).

Great job. Sub-queries have always been a mystery to me. Damn impressive how you threw that together so quickly. The query is STILL running - approaching 20 min now but the meter is still showing progress.

Holy SugarHoneyIcedTea, Batman...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 19, 2002
Messages
42,976
I would convert the data sooner rather than later because of how long the query will take. You also need to modify the login/logout code. Login should look for an empty logout and ask the user to fix the bad record. Logout has the same problem, if there is no empty record, the user has to provide a login. Those rules may be too simplistic but you do have to somehow account for both missing In's and out's.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 19, 2002
Messages
42,976
Another way of grouping the data might be to add a new column that assigns a sequence number for each in or out for the same day. Then you can join the ins to the outs for the same sequence number. This will be peachy as long as there are no missing punches. It will also help you to find the missing punches.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:34
Joined
Apr 27, 2015
Messages
6,286
My plan is to store the Logon and Logoff as TempVars and then write one "Insert Into" statement to the table. That is unless of course someone convinces me that the idea is flawed...
 

isladogs

MVP / VIP
Local time
Today, 21:34
Joined
Jan 14, 2017
Messages
18,186
Why not create a make table query based on what DBG gave you but minus the duration calculation.

That will probably be far quicker than waiting for the query results to complete
 

Users who are viewing this thread

Top Bottom