Question 208021 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL help Calculating Employee Overtime hours with 2 jobs in the same company

I’m trying to calculate regular an overtime hours for an employee that works 2 jobs at the same company. The reason it needs to be done this way, is the hours get billed to separate departments. Overtime begins after the employee works 40 total hours, and anything beyond is considered Overtime for the respective job. I basically need (4) different numbers out of the sql statement. I need:

Job 1 Regular hours
Job 1 Overtime hours
Job 2 Regular hours
Job 2 Overtime hours

My current sql query to achieve the table below is:

SELECT clockPunch.punchinTime, clockPunch.punchoutTime, clockPunch.job, ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(clockPunch.punchoutTime, clockPunch.punchinTime))/3600.0)*10)/10, 2) AS timedifference FROM clockPunch WHERE clockPunch.employee_id=1 order by punchinTime DESC

My current table looks like below:

employee    punchinTime           punchoutTime             job     timedifference
1           2023-06-22 08:00:00   2023-06-22 17:00:00      1       9.00
1           2023-06-23 09:00:00   2023-06-23 17:30:00      1       8.50
1           2023-06-24 11:00:00   2023-06-24 20:00:00      2       9.00
1           2023-06-25 09:00:00   2023-06-25 17:00:00      1       8.00
1           2023-06-26 13:00:00   2023-06-26 23:00:00      2       10.00
1           2023-06-27 14:30:00   2023-06-28 01:00:00      2       10.50
1           2023-06-28 14:00:00   2023-06-28 19:00:00      2       5.00
1           2023-06-29 09:00:00   2023-06-29 17:00:00      1       8.00

For the above data, I am trying to write a new query, but am unsure how to. I want the new query to show the following result:

Job 1 Regular hours: 25.50
Job 1 Overtime hours: 8.00
Job 2 Regular hours: 14.50
Job 2 Overtime hours: 20.00

Does anyone have any advice on how to achieve this through an sql query?

Tags:

2

Answers


  1. 0

    I don’t have time to write out a complete query to do everything you need, but this will calculate how many total hours the employee has worked after the current punch out:

      SELECT employee, 
         punchinTime, 
         punchoutTime,
         job, 
         timedifference,
         @timevar := @timevar + timedifference AS timetodate
  FROM clockPunch
  CROSS JOIN (select @timevar := 0.0) timevar
  ORDER BY punchinTime;

    After this, you can calculate how much time from the current punch is overtime by subtracting 40 from the total. Whichever value is smaller, the remainder of the total or the value in timedifference, is the overtime from that punchin.

    Then it is a matter of using some aggregate functions to sum up your overtime and regular hours by job.

    NOTE: This does not take into consideration the fact that there are times from multiple weeks in the provided data. The WEEKOFYEAR() function will help in grouping each weeks worth of data together.

    Login or Signup to reply.
  2. 0

    We can use SUM() as a window function to get the cumulative total hours:

    SELECT
    punchinTime, punchoutTime, job,
    ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(punchoutTime, punchinTime))/3600.0)*10)/10, 2) AS timedifference,
    SUM(ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(punchoutTime, punchinTime))/3600.0)*10)/10, 2)) OVER (PARTITION BY employee_id ORDER BY punchinTime ASC) AS cumulative_hours
FROM clockPunch
WHERE employee_id = 1
ORDER BY punchinTime ASC;

    Which will output:

    punchinTime punchoutTime job timedifference cumulative_hours
    2023-06-22 08:00:00 2023-06-22 17:00:00 1 9.00 9.00
    2023-06-23 09:00:00 2023-06-23 17:30:00 1 8.50 17.50
    2023-06-24 11:00:00 2023-06-24 20:00:00 2 9.00 26.50
    2023-06-25 09:00:00 2023-06-25 17:00:00 1 8.00 34.50
    2023-06-26 13:00:00 2023-06-26 23:00:00 2 10.00 44.50
    2023-06-27 14:30:00 2023-06-28 01:00:00 2 10.50 55.00
    2023-06-28 14:00:00 2023-06-28 19:00:00 2 5.00 60.00
    2023-06-29 09:00:00 2023-06-29 17:00:00 1 8.00 68.00

    Now we can use the above query as a derived table and figure out which hours are regular and which are overtime:

    SELECT *,
    CASE
        WHEN cumulative_hours <= 40 THEN timedifference
        WHEN cumulative_hours - timedifference <= 40 THEN 40 - (cumulative_hours - timedifference)
    END AS regular_hours,
    CASE
        WHEN cumulative_hours - timedifference >= 40 THEN timedifference
        WHEN cumulative_hours > 40 THEN cumulative_hours - 40
    END AS overtime_hours
FROM (
    SELECT
        punchinTime, punchoutTime, job,
        ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(punchoutTime, punchinTime))/3600.0)*10)/10, 2) AS timedifference,
        SUM(ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(punchoutTime, punchinTime))/3600.0)*10)/10, 2)) OVER (PARTITION BY employee_id ORDER BY punchinTime ASC) AS cumulative_hours
    FROM clockPunch
    WHERE employee_id = 1
    ORDER BY punchinTime ASC
) t1;

    Which give us:

    punchinTime punchoutTime job timedifference cumulative_hours regular_hours overtime_hours
    2023-06-22 08:00:00 2023-06-22 17:00:00 1 9.00 9.00 9.00
    2023-06-23 09:00:00 2023-06-23 17:30:00 1 8.50 17.50 8.50
    2023-06-24 11:00:00 2023-06-24 20:00:00 2 9.00 26.50 9.00
    2023-06-25 09:00:00 2023-06-25 17:00:00 1 8.00 34.50 8.00
    2023-06-26 13:00:00 2023-06-26 23:00:00 2 10.00 44.50 5.50 4.50
    2023-06-27 14:30:00 2023-06-28 01:00:00 2 10.50 55.00 10.50
    2023-06-28 14:00:00 2023-06-28 19:00:00 2 5.00 60.00 5.00
    2023-06-29 09:00:00 2023-06-29 17:00:00 1 8.00 68.00 8.00

    And then to pull it all together we can group by job:

    SELECT job,
    SUM(
        CASE
            WHEN cumulative_hours <= 40 THEN timedifference
            WHEN cumulative_hours - timedifference <= 40 THEN 40 - (cumulative_hours - timedifference)
        END
    ) AS regular_hours,
    SUM(
        CASE
            WHEN cumulative_hours - timedifference >= 40 THEN timedifference
            WHEN cumulative_hours > 40 THEN cumulative_hours - 40
        END
    ) AS overtime_hours
FROM (
    SELECT
        punchinTime, punchoutTime, job,
        ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(punchoutTime, punchinTime))/3600.0)*10)/10, 2) AS timedifference,
        SUM(ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(punchoutTime, punchinTime))/3600.0)*10)/10, 2)) OVER (PARTITION BY employee_id ORDER BY punchinTime ASC) AS cumulative_hours
    FROM clockPunch
    WHERE employee_id=1
    ORDER BY punchinTime ASC
) t1
GROUP BY job;

    Et voilà:

    job regular_hours overtime_hours
    1 25.50 8.00
    2 14.50 20.00

    Your demo data is Thursday to Thursday inclusive so I have not added the partitioning or grouping by week, but hopefully this enough to get you going.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search