Calculate effective hours for an employee for particular day from in-out data

PHP
1

Hi All,

I have swipe card system and data of all swipes goes to sql server. When an employee swipes card his corresponding in and/or out data goes to database. The columns are like card number, swipe_time, and channel (holds flag 2 for in and 1 for out).

I want to calculate actual effective hours from the system. I tried to get the difference of successive out - in. But what if there is mismatch in the count of total in and total out.

For. ex. ‘In’ data for 2013-05-08 is

and ‘Out’ data for the same day

In above, the in-out total count is a mismatch.
There could be another approach for this. Could you please help me out?

Your help will be highly appreciated.

Thanks in advance.

2

Spurious “out” swipe seems to be a bounce though - you’ve got an ‘out’ at 16:01:09, and another at 16:01:11, so I’d guess whoever swiped out thought the first one hadn’t registered. You could deal with that kind of situation by checking the the “out” swipe is actually after the preceding “in” swipe. So in your case you’d go in seqence:


09:37:25 in
10:07:40 out
10:08:13 in
10:53:15 out
10:54:15 in
13:02:15 out
13:24:21 in
13:26:05 out
13:57:49 in
16:01:09 out
16:02:48 in
16:01:11 out ** error, because it's before the preceding "in", so ignore it
16:31:20 out
16:32:43 in
19:00:25 out

There’s only so much you can do if the incoming swipe data cannot be relied upon, and just dismissing lines like the above suggests isn’t necessarily the best way. Also you might get more answers in another section - it’s not specifically a PHP thing really.