Wrong retrieved attendance and leave data
-
Greetings. I'm having a problem that is we have a fingerprint devices in the factory, we have 2 work shifts OK so the security guys must switch the devices from "in state" to "out state" so that the [in/out mode] in the database being 0 "means attend state" it changes to 1 "means leave state", and vice versa. The problem is that sometimes the security guys forget to change the state of the device to in mode or out mode in the proper time so all the coming records will be wrong for example an employee will have 2 records one at 8:00:00 AM with an in/out mode value 0 and another one at 4:00:00 PM again with in/out mode value 0 instead of 1 so what I want is to adjust such records so that the returned data be correct. Here is the table of the attendance and leave log We have 2 shifts on starts at 8:00:00 AM and ends at 4:00:00 PM and another starts at 6:30:00 PM and ends on the next day at 8:00:00 AM, that it is, one starts and ends in the same day and another starts on a day and ends on another day. So I want to adjust such invalid records before retrieving the data to based on work shift. HR_Attend_And_Leave_Of_Staff: [Record No.], [Machine ID], [Employee ID], [Verify Mode] having value 1 if verify mode is a fingerprint or 15 if the mode is face, [In/Out Mode] having 0 for attendance 1 for leave, [Date], [Time] HR_Employee_Job_Info: [Employee ID], [Shift ID]
-
Greetings. I'm having a problem that is we have a fingerprint devices in the factory, we have 2 work shifts OK so the security guys must switch the devices from "in state" to "out state" so that the [in/out mode] in the database being 0 "means attend state" it changes to 1 "means leave state", and vice versa. The problem is that sometimes the security guys forget to change the state of the device to in mode or out mode in the proper time so all the coming records will be wrong for example an employee will have 2 records one at 8:00:00 AM with an in/out mode value 0 and another one at 4:00:00 PM again with in/out mode value 0 instead of 1 so what I want is to adjust such records so that the returned data be correct. Here is the table of the attendance and leave log We have 2 shifts on starts at 8:00:00 AM and ends at 4:00:00 PM and another starts at 6:30:00 PM and ends on the next day at 8:00:00 AM, that it is, one starts and ends in the same day and another starts on a day and ends on another day. So I want to adjust such invalid records before retrieving the data to based on work shift. HR_Attend_And_Leave_Of_Staff: [Record No.], [Machine ID], [Employee ID], [Verify Mode] having value 1 if verify mode is a fingerprint or 15 if the mode is face, [In/Out Mode] having 0 for attendance 1 for leave, [Date], [Time] HR_Employee_Job_Info: [Employee ID], [Shift ID]
The question arises, do you want to fix the problem after the fact or change the business process before the error happens. Can you raise a dialog from the timekeeping s/w if the mode has not been switched at a specified time. Otherwise you need to look for paired 0 or 1 valued records in the database and flip the second occurrence.
Never underestimate the power of human stupidity RAH
-
The question arises, do you want to fix the problem after the fact or change the business process before the error happens. Can you raise a dialog from the timekeeping s/w if the mode has not been switched at a specified time. Otherwise you need to look for paired 0 or 1 valued records in the database and flip the second occurrence.
Never underestimate the power of human stupidity RAH
I want to call a method to correct such data within the database table before calling another one the retrieving the attendance records for employees whereas the person who is responsible for obtaining the staff attendance data. So what I want is a SQL statement(s) that correct such wrong data within the table itself before I retrieve the attendance data. Hope my idea is clear :) thanks for support in advance
-
I want to call a method to correct such data within the database table before calling another one the retrieving the attendance records for employees whereas the person who is responsible for obtaining the staff attendance data. So what I want is a SQL statement(s) that correct such wrong data within the table itself before I retrieve the attendance data. Hope my idea is clear :) thanks for support in advance
So the second suggestion is the way to go. Look for the paired 0 and 1 records in sequence within a period of time and flip sign on the second occurrence. I would break this down to a number of steps: Get the last 2 records for each person Check if the sign is the same Get the last record based on date/time. Flip it's sign 1 and 2 may be combined into 1 query
Never underestimate the power of human stupidity RAH
-
Greetings. I'm having a problem that is we have a fingerprint devices in the factory, we have 2 work shifts OK so the security guys must switch the devices from "in state" to "out state" so that the [in/out mode] in the database being 0 "means attend state" it changes to 1 "means leave state", and vice versa. The problem is that sometimes the security guys forget to change the state of the device to in mode or out mode in the proper time so all the coming records will be wrong for example an employee will have 2 records one at 8:00:00 AM with an in/out mode value 0 and another one at 4:00:00 PM again with in/out mode value 0 instead of 1 so what I want is to adjust such records so that the returned data be correct. Here is the table of the attendance and leave log We have 2 shifts on starts at 8:00:00 AM and ends at 4:00:00 PM and another starts at 6:30:00 PM and ends on the next day at 8:00:00 AM, that it is, one starts and ends in the same day and another starts on a day and ends on another day. So I want to adjust such invalid records before retrieving the data to based on work shift. HR_Attend_And_Leave_Of_Staff: [Record No.], [Machine ID], [Employee ID], [Verify Mode] having value 1 if verify mode is a fingerprint or 15 if the mode is face, [In/Out Mode] having 0 for attendance 1 for leave, [Date], [Time] HR_Employee_Job_Info: [Employee ID], [Shift ID]
Consider this logic ... A fingerprint swipe comes from the device A query is made to the database as to the last time the employee has clocked on If the employee has not clocked on in 16 hours (some value which represents the longest shift), then the tranasction should be considered an "ON" transaction; otherwise it must be an "OFF" transaction. When querying your database, if you don't find matching On/Off transactions for a given shift, then you could have an "assumed Off" which is equal to the On time plus the scheduled duration of the shift. To make this work, your really need to know the shift duration for the employee scheduled on a particular day. Hope this helps.
-
Greetings. I'm having a problem that is we have a fingerprint devices in the factory, we have 2 work shifts OK so the security guys must switch the devices from "in state" to "out state" so that the [in/out mode] in the database being 0 "means attend state" it changes to 1 "means leave state", and vice versa. The problem is that sometimes the security guys forget to change the state of the device to in mode or out mode in the proper time so all the coming records will be wrong for example an employee will have 2 records one at 8:00:00 AM with an in/out mode value 0 and another one at 4:00:00 PM again with in/out mode value 0 instead of 1 so what I want is to adjust such records so that the returned data be correct. Here is the table of the attendance and leave log We have 2 shifts on starts at 8:00:00 AM and ends at 4:00:00 PM and another starts at 6:30:00 PM and ends on the next day at 8:00:00 AM, that it is, one starts and ends in the same day and another starts on a day and ends on another day. So I want to adjust such invalid records before retrieving the data to based on work shift. HR_Attend_And_Leave_Of_Staff: [Record No.], [Machine ID], [Employee ID], [Verify Mode] having value 1 if verify mode is a fingerprint or 15 if the mode is face, [In/Out Mode] having 0 for attendance 1 for leave, [Date], [Time] HR_Employee_Job_Info: [Employee ID], [Shift ID]
Seems to me that if the security crew has to switch the on/off at the same times each day, you can easily use the time to decide whether it is an in/out punch. In fact, why is there a switch anyway? We reconcile via a pre-determined schedule for each employee. They should be at the clock at the beginning and end of each shift. If there are missing punches that correspond with these times, a supervisor has to manually reconcile the work day for the erred employee. We gave up on trying to identify a punch as in/out at the time of punch a long time ago.
-
Seems to me that if the security crew has to switch the on/off at the same times each day, you can easily use the time to decide whether it is an in/out punch. In fact, why is there a switch anyway? We reconcile via a pre-determined schedule for each employee. They should be at the clock at the beginning and end of each shift. If there are missing punches that correspond with these times, a supervisor has to manually reconcile the work day for the erred employee. We gave up on trying to identify a punch as in/out at the time of punch a long time ago.
The fingerprint device is working that way. It is manufactured with that switch button. So if the employee of a given shift was about leaving while the security guys did not change the in/out state of the fingerprint device and that employee put his finger he will have 2 records each with in state and when reading the data this employee considered did not record leaving state. On the other hand, if the employee of a second shift came before his shift start time and the security guys did not switch the device from out state "as they changed the in state to out state at the end of the first shift" and that employee puts his finger so the final result that this employee has 2 records with a leave state. Such matter of wrong records is a case sensitive as the company will depend on the data in calculating the overtime and in salary discount(s). I hope my idea is clear now. The shift 1 and shift 2 begins and ends on 08:00:00 AM - 04:00:00 PM and 06:30:00 PM - 08:00:00 AM on the next day.