Stumped on design/calculation problem, need help
I have a solution that collects time-charges for a timekeeping system. The data-entry part is working well. There is a table that collects all the individual time charges against an account by an employee. It is just called "Hours." Each record contains date created, account created, hours, account charged, employee id.
Now, I'm tasked with creating reports based on this data. One aspect of this is to calculate how Leave hours (vacation, sick, observed holidays, bereavement, etc) are divided amongst the accounts actually worked on.
For example, employee works 16 hours (2 days) on account1, and 16 hours (2 days) on account2. Then on one day in the same week she takes a paid day off (Leave) for 8 hrs. So now the system must divide her 8 hour vacation 50/50 between the two accounts she worked on before she took off a day, based on the fact that 50% of her actual work time went towards each account.
I am puzzled as to where this calculation field must go. Or if more fields are necessary to set it up and report it. Eventually I need to be able to report each employee's total time worked for a month, what percentage of their time was spent on each project/account, and finally an aggregate of all employees and how all of their time was split amongst the different accounts. Not sure if it should be global, a variable in a script, a summary, or what exactly.
Right now I established a total_time summary field which is a total of hours worked. hours_worked is a number field used by the employee for them to enter their own hours worked against a single account, on an individual day, the basis for the records in Hours.
And I have a pct_of_total_hours which is a summary field set to fraction of total of hours-worked.