1

I have got a table the capture the min/max time in the following format.

1      2012-10-30 12:13:07.000        2012-10-30 15:18:50.000   
2      2012-10-30 16:13:07.000        2012-10-30 21:18:50.000

I was wondering if it is possible to capture 2 different sets of datetime for the same user like in the following table. The reason to do this is because, the data is use to manage the attendance of contractors who can come in and out anytime doing multiple shift. ie, UID 1 can can clockin at 12:00 and clockout at 15:00 and return later and clockin again at 18:00 and out at 21:00, and they don't work on a timesheet or shift schedule. 

uid    clock                          clock                    Status  
1      2012-10-30 12:00:00.000        2012-10-30 15:00:00.000   regular
1      2012-10-30 18:00:00.000        2012-10-30 21:00:00.000   Split
2      2012-10-30 16:13:07.000        2012-10-30 21:18:50.000   regular

Any suggestion will be much appreciated.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Bladefreak
  • 331
  • 1
  • 6
  • 17

1 Answers1

0

Without knowing too much about the details of your application, I would say the best way for you to structure your data is to have each record of your table correspond to a single "Punch In / Punch Out" for each employee. From there, it would be pretty trivial to calculate the hours worked for each employee over a given period of time. I'm not mistaken, that sounds pretty much like how you've describe the data to be laid out in your second example (minus the Status column).

In the most simple example, your table would have the following columns:

UID (INT or uniqueidentifier)
ClockIn (datetime)
ClockOut (datetime)

When an employee clocked in, you would insert a row into the table for that employee and put the clock in time under the ClockIn column.

UID     ClockIn                    ClockOut
-------------------------------------------
1       2012-10-30 12:00:00.000    NULL

As each employee clocks in, they will each be given a row

UID     ClockIn                    ClockOut
-------------------------------------------
1       2012-10-30 12:00:00.000    NULL
2       2012-10-30 1:00:00.000     NULL
3       2012-10-30 2:00:00.000     NULL

When an employee clocks out, your application should retrieve the existing record for that employee, and UPDATE the row by setting the ClockOut column to the clock out time.

UID     ClockIn                    ClockOut
-------------------------------------------
1       2012-10-30 12:00:00.000    NULL
2       2012-10-30 1:00:00.000     2012-10-30 3:30:00.000
3       2012-10-30 2:00:00.000     NULL

If an employee clocks out and then returns later, they will be given a new record just as if they were clocking in earlier that day.

UID     ClockIn                    ClockOut
-------------------------------------------
1       2012-10-30 12:00:00.000    NULL
2       2012-10-30 1:00:00.000     2012-10-30 3:30:00.000
3       2012-10-30 2:00:00.000     NULL
2       2012-10-30 4:30:00.000     NULL

Hopefully, at this point you should be able to see how this rather simple data model is flexible enough to allow for most of your standard time-keeping functionality.

  • You can determine who is clocked in by looking for rows where ClockOut is NULL
  • You can have some sort of process for handling when empoyees forget to punch out
  • Editing punches for employees that clock in or out late.
  • You can determine the total hours worked for each employee by doing a DATEDIFF of ClockOut and ClockIn for each of their rows, and summing the results

In this example, most of the responsibility for validating the data would take place in your application later. You wouldn't want someone to be clocked in multiple times, or try to clock out if they haven't actually punched in yet.

mclark1129
  • 7,532
  • 5
  • 48
  • 84