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]

Is my question is too diffcult that way?

Apparently. Can you provide some sample data? Show what you have, and what you want it to be. Perhaps that will help in coming up with the right query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.