Need Ideas for Storing Time Clock information.

  • Thread starter Thread starter Charles May
  • Start date Start date
C

Charles May

Anyone have a simple concept for the best way to store timeclock information
in a database.

I currently have my table set up like this with a typical daily entry.

tcID empID Type Date Time
1 37 'Clockin' 1/18/08 7:45:39 AM
2 38 'Clockin' 1/18/08 7:55:42 AM
3 39 'Clockin' 1/18/08 7:55:42 AM
4 38 'BreakOut' 1/18/08 10:01:00 AM
5 39 'BreakOut' 1/18/08 10:01:15 AM
6 37 'BreakOut' 1/18/08 10:03:27 AM
7 39 'BreakIn' 1/18/08 10:10:10 AM
8 37 'BreakIn' 1/18/08 10:11:01 AM
9 38 'BreakIn' 1/18/08 10:13:07 AM
10 39 'LunchOut' 1/18/08 12:00:10 AM
11 37 'LunchOut' 1/18/08 12:00:45 AM
12 38 'LunchOut' 1/18/08 12:01:27 AM
13 39 'LunchIn' 1/18/08 12:55:40 AM
14 37 'LunchIn' 1/18/08 12:57:45 AM
15 38 'LunchIn' 1/18/08 12:59:11 AM
16 38 'BreakOut' 1/18/08 03:01:00 AM
17 39 'BreakOut' 1/18/08 03:01:15 AM
18 37 'BreakOut' 1/18/08 03:03:27 AM
19 39 'BreakIn' 1/18/08 03:10:10 AM
20 37 'BreakIn' 1/18/08 03:11:01 AM
21 38 'BreakIn' 1/18/08 03:13:07 AM
22 37 'ClockOut' 1/18/08 05:00:39 AM
23 38 'ClockOut' 1/18/08 05:00:41 AM
24 39 'ClockOut' 1/18/08 05:01:12 AM

The problem I'm having is parsing out the information to calculate the time
and generate a report. I'm struggling with the logic of pulling the
information for total time in a Clockin-Clockout sequence. Currently I'm
ignoring breaks but want them logged to monitor if anyone is taking longer
breaks. We pay the 10 minute breaks so I am really only looking at Total
Time - Lunch Time (of course I will adjust the start time and end time for
the work day to 8am - 5pm as needed) Keep in mind there can be an incident
where an employee has to leave sometime and which will lead to an additional
Clockin-Clockout sequence that will have to be subtracted from the total
hours for the day or the occurrence of a missing piece of data like
forgetting to clock back in from lunch etc...

I am also not locked into this database format. This is just something I
came up with and it seemed to be a good design however it's a nightmare when
trying to calculate.

Anyone done anything like this before?

Any help is greatly appreciated.

Thanks
Charlie
 
Anyone have a simple concept for the best way to store timeclock information
in a database.

I currently have my table set up like this with a typical daily entry.

tcID    empID    Type        Date        Time
1        37            'Clockin'      1/18/08    7:45:39 AM
2        38            'Clockin'      1/18/08    7:55:42 AM
3        39            'Clockin'      1/18/08    7:55:42 AM
4        38            'BreakOut'   1/18/08    10:01:00 AM
5        39            'BreakOut'   1/18/08    10:01:15 AM
6        37            'BreakOut'   1/18/08    10:03:27 AM
7        39            'BreakIn'      1/18/08    10:10:10 AM
8        37            'BreakIn'      1/18/08    10:11:01 AM
9        38            'BreakIn'      1/18/08    10:13:07 AM
10      39            'LunchOut'   1/18/08    12:00:10 AM
11      37            'LunchOut'   1/18/08    12:00:45 AM
12      38            'LunchOut'   1/18/08    12:01:27 AM
13      39            'LunchIn'      1/18/08    12:55:40 AM
14      37            'LunchIn'      1/18/08    12:57:45 AM
15      38            'LunchIn'      1/18/08    12:59:11 AM
16      38            'BreakOut'   1/18/08    03:01:00 AM
17      39            'BreakOut'   1/18/08    03:01:15 AM
18      37            'BreakOut'   1/18/08    03:03:27 AM
19      39            'BreakIn'      1/18/08    03:10:10 AM
20      37            'BreakIn'      1/18/08    03:11:01 AM
21      38            'BreakIn'      1/18/08    03:13:07 AM
22      37            'ClockOut'   1/18/08    05:00:39 AM
23      38            'ClockOut'   1/18/08    05:00:41 AM
24      39            'ClockOut'   1/18/08    05:01:12 AM

The problem I'm having is parsing out the information to calculate the time
and generate a report. I'm struggling with the logic of pulling the
information for total time in a Clockin-Clockout sequence. Currently I'm
ignoring breaks but want them logged to monitor if anyone is taking longer
breaks. We pay the 10 minute breaks so I am really only looking at Total
Time - Lunch Time (of course I will adjust the start time and end time for
the work day to 8am - 5pm as needed) Keep in mind there can be an incident
where an employee has to leave sometime and which will lead to an additional
Clockin-Clockout sequence that will have to be subtracted from the total
hours for the day or the occurrence of a missing piece of data like
forgetting to clock back in from lunch etc...

I am also not locked into this database format. This is just something I
came up with and it seemed to be a good design however it's a nightmare when
trying to calculate.

Anyone done anything like this before?
Click to expand...

Just a thought, but it might be easier if you split out the Type
information to two columns, one column for In/Out and other for
Reason.
 
Charles said:
Anyone have a simple concept for the best way to store timeclock
information in a database.
Click to expand...

Better places to ask might be

microsoft.public.sqlserver.programming
comp.databases.ms-sqlserver

where there are also answers to "how do I calculate timespan"-type
questions.

Andrew
 
your current table structure is certainly not normalized and may
present replication and / or cohesion problems in the future.

in our timeclock application, the recorded time table contains a
column for each in / out instance. so there is a dayIn, lunchOut,
lunchIn, and dayOut column.

the application itself does not allow the user select which instance
they are clocking in / out for. so, if all four columns are empty,
it's assumed that the user is clocking in for the day and the dayIn
column is populated by the application. if dayIn already contains a
datetime, then the lunchOut is populated.

of course, your sql query must return a single row for the current
date.

i suppose if you're including breaks additional columns could be
created.

i found this structure the easiest when attempting to create reports.

i'm not sure if i've been thorough enough in this description so let
me know if i can help.

joe c.
 
Charles,

How do you want to do it better, in my idea it is impossible.

Just registrate what happens and put there your business logic around.

Cor
 
Joe,
As I was testing this I decided to try just what you specified. But then I
came upon an employee having to leave sometime in the day for maybe an
emergency or a dr.'s appointment and returns to finish out the day. I guess
I could just create a couple of extra columns for extra Ins and outs to
cover this. I just thought that someone had an ideal way of handling it that
I might be over-looking.

Another idea I had was to create multiple tables and calculate the time in
the database between clockins and clockouts for each type. So for example I
would have the following tables:

WorkHours
WorkHoursID
EmpID
Date
TimeIn
TimeOut
TotalTime

LunchHours
WorkHoursID
EmpID
Date
TimeIn
TimeOut
TotalTime

same with breaks... anyway if I clockin and and there is an occurrence of a
clockin present for that employee and date then create a new row (this
should allow extra clock logs for a given date).

When a clock out occurs, find the clockin that occurred that doesn't have a
clockout entered and update the row with the time out and calculate the
total time.

Then I could just SELECT sum(TotalTime) from WorkHours WHERE EmpID = 39 and
Date = ... for each table and then minus any lunch time from it.

Does this make sense?

I guess my biggest problem is trying to make it idiot proof when indeed that
cannot happen. If someone forgets to clock in but then later has to leave
and clocks out. I need to just create an entry to show a missing clockin and
notify the operater to inform their supervisor. I have seen so many problems
with paperless time clocks in the past that I guess I'm just trying to
alleviate a lot of them and it's making it harder than it actually needs to
be. I actually had a guy come in to work one day and forgot to clock in.
About 35 minutes into the shift he receive an emergency phone call and asked
to leave, he was in such a rush he ran out of the office and didn't clock
out. When he returned 2 hours later he clocked in and never realized that to
the people in payroll it looked like he didn't come in until 10:45. I guess
I just need to play it by ear and get something that functions and then work
on trying to solve the worlds problems ;)

I'm going to test this over the weekend to see what I can come up with.

Thanks for the input,
Charlie
 
Back
Top