[AccessD] Compare Clock-In/Out to LogIn/Out

[Gustav Brock]

Hi David

> but in reality we are seeing:
> in 9, out 12. in 12:30 out 5PM, in 9PM, out 11:30PM.

How is that? I doubt they work that much, so such false entries should not be able to be created by the source system.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
Sendt: 13. oktober 2016 00:12
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>; ACCESS-L at peach.ease.lsoft.com
Emne: [AccessD] Compare Clock-In/Out to LogIn/Out

I was given a task that was taken from someone else to compare Employee's Clock-in and out times to their logIn and out times from two different software systems.

I've already explained that the Log-in/out system isn't going to be accurate as there is no true capture for logging someone out as well as other issues such as network loss, time outs and a few other things.

Ignoring all that for moment. How would you go about comparing the times?
They want to know when employees are loggin in and for how long outside of their clocked in/out times.

I imported various spread sheets (their method of delivery to me) into various SQL tables. I thought for the initial "ballpark" comparison, I would take each employee's Min Clock in and max clock out grouped by Date and compare that to each log in.
If the log in is outside of the clocked in/out times I include it. If the logged in/out times overlap the clocked in/out times, I only count the minutes that is outside of the clocked in/out times (as per their request).

A problem comes up when I assumed that everyone has a 30 minute lunch and we were going to ignore that. So a Clock in session for an employee might be :
In 9AM out 12, in 12:30 out 5PM.  but in reality we are seeing:

in 9, out 12. in 12:30 out 5PM, in 9PM, out 11:30PM.
I have actually counted 5 clockin/out sessions for an employee for a given day.

They also want to count log ins during the lunch period.

I'm thinking of doing one of two different things (hopefully one of you can come up with something easier :)  ).

Method 1.  Dump all logins into a temp table.

Fields : EmpID, Date, LogIn, LogOut

1a. Loop through each clock in/out record (same fields) and delete the row from the temp table where the login is fully engulfed by the clock in session.

1b. Loop through and modify the log in or out time where it overlaps the clock in/out session, leaving only the outer login/out times.

my resulting list in the temp tables would be the login/out times outside of clocked sessions.


or method two:

Create a table of every minute (1440) in the day.
insert the PK of each Clock session  and ID of each Minute of the day into a Clock temp table and do the same for the log ins.

Compare the temp tables by minute.

any thoughts?

TIA David