Solved Determine time of activity.

[NauticalGent]

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?

 

[theDBguy]

Nah, this should be possible using queries. Are you looking for the total time for the day or each individual session?

 

[NauticalGent]

Individual session...

 

[NauticalGent]

Nah, this should be possible using queries

I thought so too, but I ran out of ideas quick. The cartesian joins could not be measured with modern technology!

 

[theDBguy]

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]

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]

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]

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.

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

 

[NauticalGent]

Did Anybody See This Coming Who Knew GIF - Did Anybody See This Coming Who Knew Not Expected - Discover & Share GIFs

Click to view the GIF

tenor.com

 

[isladogs]

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]

problems still occur where a user doesn't logout due to e.g. an illegal shut down or a power failure.

There are at 6,000 rows that fit this bill...

 

[NauticalGent]

Here is the actual data. Takes a LONG time to execute!

 

[theDBguy]

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]

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]

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]

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.

 

[NauticalGent]

restructure your table

It is my (inherited) table - but I will make it better now that I can save the data. Again, great job and thanks!

 

[Pat Hartman]

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]

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]

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