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

[David McAfee]

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