Hi All - I am looking for advice on how to best calculate total hours worked per day on a Sunday per worker. I have an eSQL that will find that date for me.
I have searched the forums here and each case appears to be slightly unique to mine or the thread was incomplete with no response back from the requestor.
I have a timesheet table which has a lines table, a new record in the lines table is created for each worker for that day and their time for that day is recorded in that record.
Normally a user will have 5 line records a week recorded against their name. A worker can be assigned against a timesheet with multiple workers.
What I want to do now is total that time for the week on a per user basis but at a loss as to where this should be done. I have some thoughts but before I go down a rabbit hole I thought I would ask for some guidance or suggestions.
- I think I will need a new table for this? Maybe called Payroll?
- For each week ending there would be a single record and all workers who were assigned to a timesheet would have their hours combined into a total.
- I think a script will need to be run on the server and pull all the data together in a single record per user for that week?
- Lastly, I was hoping that perhaps an eSQL statement might solve the issue for me, I can use eSQL to pull record data from tables but I have no idea where to start on this.
- Could a single eSQL statement solve my issue? as a calculation field in a table where I would script the creation of a weekending record for each worker listed in the system?
I have attached a sample file with two timesheets, each with 5 workers assigned - as a reference.