Jump to content

Calculating Days Off and Hours Per day


Recommended Posts

Okay, I want to bounce this off you experts cause I find I usually go about things the hard way so I want to see if this is the best way to do what I am trying to do.


I am trying to track our employee's vacation requests and time off.  The issue I have is not everyone works Monday through Friday and not everyone works the same number of hours per day.


For instance, I may work 8 hour days monday through friday, but Bob may work Sunday Monday, have Tuesday off work Wednesday Thursday and Friday and have Saturday off.  He works 7 hours every day except for Sunday which he works 3 hours on.  When he puts in a time off request, I want to be able to calculate the number of hours he is requesting.  What I am doing for the Monday through Friday employees is doing the date diff between the dates then adding 1 and multiplying it times the number of hours the employee is working each day to get the total number of hours they are requesting off.  I am using a table with all the weekend and holidays in it and get a count of days that fall within the request days that are either weekend or holidays and subtract those out.  I want it to be more flexible going forward and want it to allow me to work in a schedule like Bob's schedule.


Here is what I was thinking might work:


Have a table that shows date, date type like this


date_id     date     date_type
1     2008-01-01     1
2     2008-01-02     2
1     2008-01-03     3


The have a table that shows date type 1 as a Sunday date type 2 as a monday, date type 3 as a tuesday, etc.


Then for the employee have date_type_1, date_type_2, date_type_3, etc and have date_type_1_hours, date_type_2_hours, date_type_3_hours, etc. 


Then I could say exclude days that they don't have matching date types for and then calculate the times based on the date_type_1 hours.


Does this sound like it will work?  Any thoughts on an easier way to do this?


Thanks for any help you can give on this.  ;D ;D ;D



Link to comment
Share on other sites

Consider adding a column to the job table to identify the number of hours per week the employee works.  If the number of hours per week an employee works is not fixed then the onus must be on the employee to provide the amount of paid leave days and a managers approval of the request.  The use the number hours per week stored in the job table or the employee provided paid days in leave to calculate the total amount paid to the employee.

Link to comment
Share on other sites

We had considered just having a number in there, but were running into problems if the employee just wanted a day off or a few hours off.  So that is why we are trying to get down to the number of hours per day in case they want say two days off, but not every day off.  The big issue I see is if they put in say January 1st 2008 to Jan 5th 2008 as the time they want off.  How do I tell what days of the week they are asking for off?

Link to comment
Share on other sites

The number of hours per day can be worked out using the `contract_type.weekly_hours`.

There is no way to tell what days an employee is asking for off using `leave.paid_days`, just calculate hours - otherwise include a date range to track the actual dates.


















leave.paid_days <- used without a leave_range table

leave.status <- (pending, approved, denied)








An employee submits a leave form starting on 11-JUN-2007 and returning to work on 25-JUN-2007 and also submits the paid date ranges (calendar controls would be handy) 11-JUN-2007 to 15-JUN-2007 and 18-JUN-2007 to 22-JUN-2007 - the resulting record would be:


leave.id = 6448

leave.employee_id = 487

leave.start_date = 11-JUN-2007

leave.return_date = 25-JUN-2007

leave.status = pending


leave_range.leave_id = 6448

leave_range.start_date = 11-JUN-2007

leave_range.end_date = 15-JUN-2007


leave_range.leave_id = 6448

leave_range.start_date = 18-JUN-2007

leave_range.end_date = 22-JUN-2007


Either way, with variable rosters, the onus is on the employee to provide accurate information and an additional approval system should be used for data integrity.


EDIT: use datetime fields in the date ranges to track leave in hours format... You are likely going to really need to have the additional leave_range table if something like leave hours needs to be tracked... either way, add an approval system also.

Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.