Job / Project Hours Database - Editing Posts

Question

I put together a very simple work / project hit / time for employees to hit and leave. Each line contains in and out punch. However, sometimes employees forget to punch or go out at the appropriate time, so I need to change their punches.

My question is: how do I verify that the changes I'm going to make do not overlap with the existing hit?

EDIT:

The tab looks something like this:

Punch ID,Project Number,Employee ID,DateTime In,DateTimeOut 138,PA15212,1234,1/1/2010 1:00,1/1/2010 5:45 139,AD15217,5678,1/1/2010 1:00,1/1/2010 3:15 140,SL15222,4567,1/1/2010 1:00,1/1/2010 2:30 141,GA15151,5678,1/1/2010 3:20,1/1/2010 5:45 

EDIT 2

To clarify, as I said in the commentary, the database is a system for tracking working hours / projects. In principle, the employee strikes at work. Once they do, their only option is to punch. Then they can transfer to the next job they are working on ... and will do this with multiple jobs during the day. The presence of In and Out punching on the same record is a simple / uncomplicated system and makes it easy to combine IN and OUT beats, as well as calculate the time in this record. However, sometimes they forget to make their way to work and end up doing it so late or forget to hit. I need to change the strokes there, but I want the changes to not overlap with the existing time frame for this employee.

Answer 1

I think you are trying to combine too many things into one table, and this complicates the situation. The main problem with the proposed design is that there is no audit trail, so it would be difficult to prove that the data is valid if there is ever a dispute about billing or an employee.

I would suggest using a real transaction log (select / insert only), which records both the person whose time is taken into account and the person who entered the data in combination with the data view, which receive the summary information that you are looking for. Then you can check conflicts by going through a magazine to look at related events (“punches”).

Answer 2

FWIW I think you only need to record what actually happens if someone forgets to punch or go out, then there should be no corresponding entry for this.

if you model what is really happening, you can always create your default values ​​for missing data on the fly based on any of your business rules, either in your application or through a stored procedure. It also allows you to change what should be the default, as you always calculate them. In addition, it allows you to receive reports / statistics, etc., When this happens, who is the offender, etc. Plus, it’s important that your database represents what actually happened, and not some kind of fiction.

 PunchID, EmployeeID, ProjectNbr, Timestamp, Direction 111, 111, 101, 1/1/11 8:00, IN 111, 111, 101, 1/1/11 17:00, OUT 

Answer 3

SQL to search for overlapping records (the trick is to join the table with yourself):

 SELECT * FROM TABLE_XXX x1, TABLE_XXX x2 WHERE x1.employee_id = x2.employee_id AND x1.DateTimeIn < x2.DateTimeIn AND x1.DateTimeOut > x2.DateTimeIn AND x1.DateTimeIn > ? -- start of the day in question AND x1.DateTimeIn < ? -- end of the day in question AND EMPLOYEE_ID = ? 

My 2c by design:

• If you keep the current design, you must create insert and update triggers that check for overlap and throw an exception if it is thrown.

• Keep the start time of each action as suggested by PerformanceDBA. This means that you must have a special project for stamping. You corrected missed hits by inserting with the time of the missing stroke. This solution will also ensure the absence of holes in time, with the exception of intentional ones (where the activity is a special activity called “kick”). This is the best design IMHO.

Answer 4

• There are several problems with the “table”, which makes it difficult for you to work.

  • What is the value of a missing punch? how did you create or default for employees who scored but didn't hit? Zero? 23:59? DateTimeIn? They are all incorrect, but you must have a known meaning.)

  • When you answer this, we can code SQL (this is really a SQL question).

  • what value would you like to update the missing punch out time with:

  • Next run minus 5 minutes

  • where is the next start time not? 17:00?
    .

• The design of the AFA database is related, and without seeing the other tables, the table should be next. Suppose they break through IN and OUT for each project (job?).

   CREATE TABLE Punch ( PunchID, -- No idea what use it has, but I will keep it anyway ProjectNo, EmployeeID, DateTime, IsStart BIT -- boolean, tinyint ) 

  • When Punch IN occurs, Punch OUT did not happen, so any value you store for it is false.

  • missing lines are easy to identify

• The external interface, graphical user interface, character-based command, regardless of whether they could improve a bit.

  • it must explicitly break through IN or OUT

  • when Punch IN is registered, if there is no previous Punch OUT, then the default series s can be used, and Punch OUT is created first. For example, on the same day: use the current day-time, the next day: use 17:00 of the previous day.

Answer 5

Here I want to add clarity to your question. Comment on what I misunderstood and missed. I will try to constantly review.

You are looking for a solution for two exceptions in the "Punch" event. The two exceptions you want to cover are:

• Employee makes a punch but forgot to punch this morning

• The employee makes a shot, but forgot to break yesterday

  • As in the first exception, the information you can use to create logic is the current punch time. If the current time is after, say, at 12 o’clock, then fill out the standard strike today at 9 o’clock.

  • The second stroke can be handled in a similar way; if the current time passed 03 at night, then the employee probably forgot to break yesterday.

And, as you probably see, I understand that this simple logic is probably not the answer you are looking for. But comment on what I missed here and I will revise it according to your needs.

Answer 6

How about this for psuedo code:

 onPunchIn() { if (DateTimeOut is null and now() < tomorrow()) { Set DateTimeOut = now() Create new record } if (DateTimeOut is null and now() > tomorrow()) { Set DateTimeOut = getDefaultEndOfDayFromYesterdafy() Create new record } } 

Answer 7

When you ask: "I need to change the strokes there, but I want the changes to not overlap." Solution: (1) change / add punch → (2) validate → (3) save or not

Generally, you should not add punch to an employee without a “close” without EndDate. Then → if all hits are “closed”, you can add a new one with StartDate more than any other EndDate employee, but if it happens that one day the employee forgot the stamp (in and out), you have to add the “new old”, breakdown between others - like this: you are a chooose employee, set StartDate and EndDate, the project id and others →, and you check the DB if:

• there is StartDates or EndDates in the database between your StartDate and EndDate -> overlap (another hit starts or ends inside your new hit)

• there is a StartDates lass than your StartDate and EndDate bigger than your EndDate -> overlap (your new hit is inside another hit)

all these queries in SQL must be performed for one employee (one employee cannot be in many projects at a time)

If none of these errors appear, you can safely add the punch. I litter for some pseudo-code and my English. Hope this was a little helpful.

Answer 8

First, regarding your question, the way to solve this problem from the point of view of the circuit is to keep the previous timeout for each row. The trick for this project is a unique constraint on ProjectNumber, EmployeeId and DateTimeIn and a foreign key reference for ProjectNumber, EmployeeId and PreviousDateTime for ProjectNumber, EmployeeId, DateTimeOut. It should also be noted that I rely on unique constraints that allow a single null, which is not true in all database systems (of course, I also rely on the database system, observing control restrictions, which is also not true for all database systems).

 Create Table Punches ( PunchId int not null Primary Key , ProjectNumber varchar... , EmployeeId int not null.. , DateTimeIn datetime not null , DateTimeOut datetime null , PreviousDateTimeOut datetime null , Unique ( ProjectNumber, EmployeeId, DateTimeIn ) , Unique ( ProjectNumber, EmployeeId, DateTimeOut ) , Unique ( ProjectNumber, EmployeeId, PreviousDateTimeOut ) , Check ( DateTimeIn <= DateTimeOut ) , Check( PreviousDateTimeOut <= DateTimeIn ) , Foreign Key ( ProjectNumber, EmployeeId, PreviousDateTimeOut ) References Punches( ProjectNumber, EmployeeId, DateTimeOut ) ) 

The surface of this approach is that the circuit itself prevents overlapping. The downside is that inserts are a bit more complicated. The first row for this employee for this project will have to use the null value for PreviousDateTimeOut, since we will not have the previous row. Secondly, this means that for punching you will need to find the previous datetime

 Insert Punches( ProjectNumber, EmployeeId, DateTimeIn, PreviousDateTimeOut ) Select ProjectNumber, EmployeeId, CURRENT_TIMESTAMP , Coalesce( ( Select Max( DateTimeOut ) From Punches Where DateTimeOut Not Null ) , CURRENT_TIMESTAMP ) 

The above just solves the overlap problem. However, this does not necessarily solve the problem of a forgotten hit, except to prevent punching without previous punching, preventing the insertion of two rows for the same ProjectNumber and Employee and null DateTimeOut. What should happen may include more complex business rules.