Calculate Working hours 1

[CHTHOMAS]

Hello Everybody,

I have a table with 4 fields.

Employee Number, Date and Time, Statusid and Status Description. Sample data is given below.

873001 28/11/00 16:00:02 1 IN
873009 28/11/00 16:01:20 4 OUT FOR THE DAY
830000 28/11/00 16:01:32 2 OUT FOR BUSINESS
830000 28/11/00 16:01:37 4 OUT FOR THE DAY
873220 28/11/00 16:01:55 3 OUT FOR PERSONAL
873102 28/11/00 16:02:19 5 OUT FOR LUNCH
356461 28/11/00 16:06:22 1 IN
356461 28/11/00 16:13:46 4 OUT FOR THE DAY
356461 29/11/00 12:58:25 0
103002 29/11/00 13:06:50 2 OUT FOR BUSINESS
103002 29/11/00 13:08:34 1 IN
830000 29/11/00 13:10:04 4 OUT FOR THE DAY
103002 29/11/00 13:33:12 4 OUT FOR THE DAY
103002 29/11/00 13:36:11 1 IN
103002 29/11/00 13:37:53 5 OUT FOR LUNCH
103002 29/11/00 15:33:17 1 IN
103002 29/11/00 15:33:36 4 OUT FOR THE DAY

The table gets the values as output of a time keeping machine. I want to calculate the number of hours an employee worked per day/week/month basis.

Whenever an employee comes to the office in the morning he swipes his card and his status is IN. When an employee goes out either for lunch or business or personal he swipes his card and gets the appropriate status. when he comes back again he swipes and his status is IN.

There can be several IN for an employee the same day.

My problem is how to calculate the hours worked by an employee?
Ie (OUT FOR THE DAY) - (IN) (ie The first IN in the Morning). However the PERSONAL OUTS and LUNCH OUTS can't be calculated as part of the Working time. So i have to exclude it. Also i have to include BUSINESS OUT as part of the Working hours. How to do it?

Any logic or ideas or solutions will be highly appreciated.

Regards and Thanks

Charley

 

[MichaelRed]

Charley,

From the sample data, this is going to get more questions than answers.

Emp 103002 goes out for business (11/29/00 1:06:50 PM) before he comes in (11/29/001:08:34 PM). What 'Assumption' do amke about his prior status?

Also, emp 830000 on 11/28/00 doe the same 'trick', but then leaves again on 11/29/00 - without comming back 'In', so how do get a starting (date &) time?

Emp 837001 only ever comes 'IN', Does he/she get colcked on to infinity (or at least the current data/time of reporting)?

In the reverse, Emp 873109 only went out for the day, having never checked IN?

Emp 873102 only went out for 'Lunch' - but was never "in" and never returned (NO 'IN').

Emp. 873220 only for 'Personal', never "IN".

While I assume this is just a fragment, I also believe that Reports which are derived from the time clock will usualy have the fragmented histories of employees which need to 'reconciled' in a reasonable manner. This MAY be accomplished by having some reporting function which simply point out the problems and a manual updating of the table to reflect the reality, or it may include certain 'rules' (assumptions) regarding employee hours. It could include a combu=ination of both.

BUT, it probably cannot give you a sensible report on the sample data as shown.





MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[dneufarth]

Have to agree with MichaelRed.

Looks like the typical batch processing report solved by procedural languages like COBOL. Whatever the language, the validity checking and error catching logic easily amounts to more code than the straight forward calculations you want.

No simple solution.

Dave

 

[alley]

I wrote a system like this several years ago, and it worked well, but there were very specific rules. The program worked in real time, and was up 24 hrs/day, using 24 hour clock. Rules:

Can't check in if you haven't checked out. (would not accept the transaction).

Can't check out if you haven't checked in.

No employee can work (clocked in) for more than 12 hours, unless he/she clocks in and out. (Not allowed to check in within 30 minutes of checkout.)

The timekeeper had a back door to enter forgotten in's and out's, with approval of the supervisor.

The calculation of hours worked is straightforward. Diagnostics were zero for anomalies, because of the rules.

The only diffference from yours is checkout for business purposes, which is not material for payroll timekeeping. Who is "IN" and who is "OUT" of the office is a different thing from timekeeping, anyway.

Too rigid for your situation?

 

[CHTHOMAS]

Thanks to all

Now i know that i am in more and big trouble.

Regards

Charley