Eliminate Errors

Here is what I am struggling with.

This is a clocking system and I want to take out the errors.

Trying a select statment to get all the records where there is a duplicate "clock in" or duplicate "clock out" for the same user.

You have an employee_ID and a direction.

The data looks something like this:

ID.......employee_ID.........Direction

 1. .........1..................In
开发者_如何学Python 2. .........2..................In
 3. .........3..................In
 4. .........1..................Out
 5. .........2..................Out
 6. .........3..................In

So record 6 would be an error.

---

as you say its a clocking system, you need to have a timestamp or a auto increment id column, based on which you can check the last event (latest timestamp or the max id) for every emp Id before inserting (as part of trigger).

---

You could also think about having one row for each shift. This row would hold both the clock-in and clock-out timestamp. Your datediff operations would be much less complex with this design.