User:Rfs/Other Other Scratch Page

To estimate breakdown of hours spent on programs:

• select time frame
• get a summary of all hours worked by job in time frame (from skedujlnator) (A)
  • SELECT work_shifts.kind, jobs.name, SUM(end_time - start_time) AS work_hours FROM work_shifts LEFT JOIN jobs ON work_shifts.job_id = jobs.id WHERE kind <> 'Unavailability' GROUP BY 1, 2 ORDER BY 1, 3 DESC;
• get a summary of all hours worked by worker in time frame (from skedujlnator) (B)
  • SELECT workers.name, SUM(end_time - start_time) AS work_hours FROM work_shifts LEFT JOIN workers ON work_shifts.worker_id = workers.id WHERE kind <> 'Unavailability' AND name <> '0 nobody 0' GROUP BY 1 ORDER BY 1;
• get actual hours reported by worker time frame (from staff worksheet in payroll) (C)
• note difference by worker (essentially C - D). This is "flex" time (D)
• put (A) and (D) in spreadsheet in first and second columns
• define programs and descriptions
• create a column for each program
• create a column for overhead/admin (non-program)
• for each row, define and record how each job (or person) breaks down into programs (and admin)
• check amounts by creating a total column for all program totals and a column that shows the difference between the program totals and the original and the second column
• correct any mistakes
• create a total row showing amount of time spent on each program
• if each person makes the same amount (or near enough) you can use the program totals to create a pie chart showing the breakdown by program
  • these percentages can be applied to staffing costs as a start
  • you will need to add to them other costs (that are not staffing related)