Job/Project Hours database - Editing Records

Question

I put together a very simple job/project punch/time clock for employees to punch in and out of. Each row contains an in and an out punch. However, sometimes employees forget to punch in or out at the appropriate time, so I have to modify their punches.

My question is: how do I check to make sure the change I'm going to make doesn't overlap with an existing punch?

EDIT:

The tabel 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 have in a comment below, the database is Job/Project Hours Tracking System. Basically the employee punches into a job. Once they do so, their only option is to punch out. Then they can punch into the next job they work on... and will do this with several jobs throughout the day. Having the In and Out punch on the same record is an easy/uncomplicated system and makes it easy to match IN and OUT punches as well as calculate time in that record. However, sometimes they will forget to punch in to a job and will end up doing so late, or forget to punch out. I need to modify there punches but I want to make sure the change doesn't overlap with an existing in/out time frame for that employee.

Answer 1

When you ask: "I need to modify there punches but I want to make sure the change doesn't overlap" the solution is: (1) modify/add punch -> (2) validate -> (3) save or not

Generally you shouldn't add punch for employee without "closing" those without EndDate. Then -> if all punches are "closed" you can add new with StartDate bigger than any other EndDate of employee - but if it happen that one day employee forgot to stamp (in and out) you will have to add a "new old" punch between others - so: you chooose employee, set your StartDate and EndDate, project id, and others -> and than you check in DB if:

1. there are StartDates or EndDates in DB between your StartDate and EndDate --> overlap (other punch starts or end inside your new punch)

2. there are StartDates lass than your StartDate and EndDate bigger then your EndDate -->overlap (your new punch is inside other punch)

all these queries in SQL must be done for one employee (one employee can't be in many projects at one time)

if none of this errors appear you can safety add punch. I'm sory for some pseudocode and my english. I hope it was usefull a little.

Answer 2

First, with respect to your question, the way to solve this from a schema standpoint is to store the previous time out for each row. The trick to this design a unique constraint on ProjectNumber, EmployeeId, and DateTimeIn and a foreign key reference on ProjectNumber, EmployeeId and PreviousDateTime to ProjectNumber, EmployeeId, DateTimeOut. It should also be noted that I'm relying on Unique constraints allowing a single null which is not true in all database systems (of course, I'm also relying on the database system honoring check constraints which is also not true in 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 upside of this approach is that the schema itself prevents an overlap. The downside is that inserts are a little trickier. The first row for a given employee for a given project will need to use a null for PreviousDateTimeOut since we won't have a previous row. Second, it means that a punch-in will require finding 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 simply solves the problem of overlap. However, it does not necessarily solve the problem of a forgotten punch out other than to prevent a punch-in without a previous punch-out by preventing the insertion of two rows for the same ProjectNumber and Employee and null DateTimeOut. What should happen might involve more complicated business rules.

Answer 3

SQL for finding overlapping records (the trick is to join the table with itself):

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 on the design:

1. If you keep the current design you should create insert and update triggers that check for overlaps and throw an exception if one is created.

2. Only store the start time of each activity as suggested by PerformanceDBA. That means you must have a special project for punching out. You would fix missed punches by doing an insert with the time of the missing punch. This solution will also ensure that there are no holes in the time except for intentional ones (where the activity is the special "punch out" activity. This is the best design IMHO.

Answer 4

FWIW I think you should only capture what actually happens, if someone forgets to punch in or out then there should be no corresponding entry for that.

if you model what is really happening then you can always create your defaults for the missing data on the fly based on whatever your business rules are either in your application or via a stored proc. This also allows you to change what the defaults should be, as you always calculate them. Plus it allows reports/stats etc of when this occurs, who is the offender etc.. plus, importantly your DB represents what really happened, not some fudge.

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

Answer 5

How about this for the 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 6

Here's my take on adding clarity to your question. Comment on what I've understood wrongly and missed. I'll try to revise continuously.

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

1. An employee makes a punch out but forgot to punch in this morning

2. An employee makes a punch in but forgot to punch out yesterday

   • As for the first exception, the information you can use to create logic is the current time of punching. If the current time is after, say 12 o'clock sharp, then fill up a standard punch-in of today at 9 o'clock.

   • The second punch can be handled similarly; if the current time is past 03 at night, then the employee has probably forgotten to punch out yesterday.

And as you probably see, I understand that this simple logic is probably not the answer you're looking for. But comment on what I've missed here, and I'll revise it to suit your needs.

Answer 7

I think you're trying to combine too many things into one table and that is making this more complicated. A major 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 were to ever be a billing or employee dispute.

I would propose the use of a true transaction log (select/insert only) that records both the person whose time is being accounted for AND the person who entered the data combined with a view of the data that derives the summary info you're looking for. Then you could check for conflicts by walking through the log to look at adjacent events ("punches").

Answer 8

1. There are several problems with the "table" that make things difficult for you.

   • what is the value of the missing punch ? how did you generate or default that for employees who punched in but did not punch out ? Null ? 23:59 ? DateTimeIn ? They are all incorrect but you must have some known value.)

   • When you answer that, we can code the SQL (it really is an SQL question).

   • what value would you like to update the missing Punch OUT time with:

   • Next startime minus 5 mins

   • where there is no next start time ? 17:00 ?
     .

2. AFA database design is concerned, and not having seen the other tables, the table should be as follows. Assuming they punch 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 the Punch IN happens, the Punch OUT has not happened, so any value you store for that is false.

   • missing rows are easy to identify

3. The front end, GUI, character-based command, whatever, could use a bit of improvement.

   • it should explicitly punch IN or OUT

   • when a Punch IN is registered, if there is no previous Punch OUT, then a series of default s could be used, and a Punch OUT created first. Eg same day: use the current datetime, next day: use 17:00 previous day.