Has anyone out there had experience in sorting out employees based on their hire and termination dates? My interests lie in going further than just filtering out employees with certain hire/term dates. What I need to do is for a given date range, have salaried employees either factored in or not based on whether they were working during all OR part of the date range. Do you understand what I'm asking? Maybe an example will help.
Facility Production Labor Cost - Salaried Report
SalariedEmployee1 Hired 1/1/04 Term: None
SalariedEmployee2 Hired 11/12/03 Term: 1/1/04
Salaried Employee3 Hired 2/2/04 Term: None
for the dates 12/1/03 - 6/1/04
A query would just figure up their annual salary, divide it by 365 and multiply it by the days they were working during the defined dates, 12/1/03 - 6/1/04. What I don't know how to handle is figuring out how many days they were actually employed between the dates 12/1/03 and 6/1/04. Employee1 will have only worked from 1/1 to 6/1. #2 goes from 12/1/03 to 1/1/04. #3 only goes from 2/2/04 to 6/1/04.
See my dilemma? I'm sure this isn't too easy of a task to tackle, but I don't know really where to start. But it seems as though this would be a common problem. Any suggestions would be GREATLY appreciated. Thanks!
This assumes there is a table called 'Salaried' with fields for 'Employee', 'HireDate', TerminateDate', and 'Salary'.
SELECT Salaried.Employee, IIf([terminatedate]<[Range_Start_Date],"N",IIf([hiredate]>[Range_End_Date],"N","Y")) AS Include, IIf([hiredate]<[Range_Start_Date],[range_start_date],[hiredate]) AS FromDate, IIf([terminatedate] Is Null,[range_end_date],IIf([terminatedate]>[Range_End_Date],[range_end_date],[terminatedate])) AS ToDate, round((CDate([todate])-CDate([fromdate])+1)*[salary]/365,2) AS SalaryPaid
When this is run, you will be asked to enter 2 parameters; 'Range_Start_Date' and 'Range_End_Date', which in your example are 12/01/03 and 6/01/04.
Hope this helps.
"Conscience is the inner voice, which warns us that someone may be looking." - Henry Louis Mencken
The CDate function converts a valid date and time expression to the variant of subtype Date, and returns the result.
On your second question, you are right about not needing the Include field. I originally set this up as a select query and created the Include field so I could use it for the select logic and was just checking to make sure the logic was working, but I should have unchecked the field prior to converting to SQL. My mistake. You can leave it out since it is not necessary.
I have another question for you that goes along with labor cost. I also have to figure up the cost of those employees who are hourly. I haven't quite figured out the structure yet of how to do this. The primary difficulty is the fact that their wages can change. If a report is generated after a wage change but is based on work that was done before the wage change then the labor cost will be erroneous.
I have thought about a table holding the employee number, the date of the wage change and the new wages. The problem with this is simplicity. Designing a query that will divide the date range into chunks based on the wage change dates and figure out the corresponding labor cost for that chunk and THEN sum them together seems like a rather large, complicated task.
Even though it would break normalization, I have thought of placing in the labor hours table two fields for regular payrate and overtime payrate which would be copied over from the EmployeeInfo table which holds these values. That way it would have an accurate payrate for those labor hours on that date. I hope I'm making sense with my explanation here.
I don't believe the second option would cause a disproportionate increase in database size by adding two integer values, which is bigger than only byte values, per each record. Plus, with deadlines approaching, the quicker way is the better way at the moment.
In your example that you gave, what if there is no term date for the pure reason that the person hasn't been terminated? Does this code still work? I'm thinking it wouldn't know how to handle a date being compared to a null/empty string. I guess that it could be handled in the end date function. Got a suggestion?