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...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.