Table design question related to time....

[sac11585]

Currently I am developing a timekeeping system that requires it to display 15-increment time span for each employee in his/her timesheet/reports. This is what I've done in the SQL Server DB for looking up/display individual start/end time...

TimeClock Lookup table
Regular Decimal
12:15 AM 0.25
12:30 AM 0.5
12:45 AM 0.75
1:00 AM 1
1:15 AM 1.25
1:30 AM 1.5

When employee keys in their start/end time, I stored them in the decimal format (in Timesheet table), so I can easily determine the exactly time the employee worked. I used the TimeClock table to inner join with the timesheet table whenever I need to display the start/end time in the report/front end in the regular format.

Well...everything is cool but now the business users require the timekeeping system to have an increment of 6 seconds (instead of 15 minutes).

My questions to all of you are.... what is a good solution to handle this situation? Should I update the TimeClock table to the increment of 6 seconds? What if later on the business rule is changed again into a different time increment? Is there a better approach in designing this TimeClock lookup table? Hope this make sense to all of you. Thanks in advance.

 

[donutman]

Time and attendance software is available off the shelf. IMO, it's a poor management decision to reinvent the wheel. I wrote one in the 80's, now I use an off-the-shelf package that uses biometrics (finger prints of sorts).
The answer to your question is to store the time as a smalldatetime or datetime. Do the rounding and the calculations in the application. T-SQL is not the place for all of the logic that goes into payroll calculations, however, if you just talking about summary reports or labor analysis that a [green]horse[/green] of a different color.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[sac11585]

Thanks for the sugguestion donutman. Our company policy has many/complex rules when it comes to the timekeeping/payroll system. Our current system also tie with budget related information as well. IMO, it requires a lot of work even if we could purchase an off-the shelf package to use.

Are you suggesting I should keep updating the TimeClock table for the increment of 6 seconds and store the regular time in a smalldatetime or datetime?

 

[donutman]

A TimeClock lookup table is an interesting approach. It never occurred to me to do that so I can't say for sure that it would be less efficient. I would think it's best to store data in it's most accurate form, then round it later. But I think a good argument can be made for rounding it at the time of collection and storing that.
If it was my choice I guess I'd do both. Have an ActualPunchTime and a ComputedPunchTime. This is especially reasonable since the ComputedPunchTime might be a reflection of their scheduled time, ie. 6:40am might have to become 7:00am if they aren't allowed to punch in early.
I wouldn't store time clock data as minutes or seconds in an integer field, if that's what you are asking.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]