dprichard Posted June 1, 2007 Share Posted June 1, 2007 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 Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted June 2, 2007 Share Posted June 2, 2007 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. Quote Link to comment Share on other sites More sharing options...
dprichard Posted June 2, 2007 Author Share Posted June 2, 2007 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? Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted June 2, 2007 Share Posted June 2, 2007 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. job_association.id job_association.job_id job_association.employee_id job_association.contract_type_id etc.. contract_type.id contract_type.label contract_type.weekly_hours contract_type.overtime_multiplier etc... leave.id leave.employee_id leave.start_date leave.return_date leave.paid_days <- used without a leave_range table leave.status <- (pending, approved, denied) etc... leave_range.leave_id leave_range.start_date leave_range.end_date etc... 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.