如何通过SQL计算同一公司内身兼两职员工的正常工时与加班工时

Problem Description #

I need to calculate regular and overtime hours for an employee who holds two positions within the same company, with hours allocated to their respective departments. The overtime rule is: once the employee's total weekly hours exceed 40, any additional hours count as overtime for the corresponding position. I need to get four values via SQL: regular hours for Job 1, overtime hours for Job 1, regular hours for Job 2, overtime hours for Job 2.

My current SQL query to retrieve punch data 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

The resulting dataset looks like this:

My desired output is:

• Job 1 regular hours: 25.50
• Job 1 overtime hours: 8.00
• Job 2 regular hours: 14.50
• Job 2 overtime hours: 20.00

Solution #

Here's an SQL approach that uses common table expressions (CTEs) and window functions to track cumulative hours and allocate regular/overtime correctly:

WITH punch_data AS (
    SELECT 
        job,
        timedifference,
        punchinTime,
        -- Calculate cumulative total hours up to each punch (ordered by clock-in time)
        SUM(timedifference) OVER (ORDER BY punchinTime) AS cumulative_hours
    FROM (
        SELECT 
            punchinTime,
            job,
            ROUND(CEIL((TIME_TO_SEC(TIMEDIFF(punchoutTime, punchinTime))/3600.0)*10)/10, 2) AS timedifference
        FROM clockPunch
        WHERE employee_id = 1
    ) AS base_hours
),
job_hour_breakdown AS (
    SELECT 
        job,
        -- Calculate regular hours for this punch: if we haven't hit 40 total yet, take the hours needed to reach 40 or the punch hours, whichever is smaller
        CASE 
            WHEN cumulative_hours - timedifference < 40 THEN 
                LEAST(timedifference, 40 - (cumulative_hours - timedifference))
            ELSE 0
        END AS regular_hours,
        -- Calculate overtime hours for this punch: the portion of the punch that pushes total hours over 40
        CASE 
            WHEN cumulative_hours > 40 THEN 
                GREATEST(0, cumulative_hours - 40) - GREATEST(0, (cumulative_hours - timedifference) - 40)
            ELSE 0
        END AS overtime_hours
    FROM punch_data
)
-- Aggregate by job and pivot to get the final four values
SELECT 
    SUM(CASE WHEN job = 1 THEN regular_hours ELSE 0 END) AS job1_regular_hours,
    SUM(CASE WHEN job = 1 THEN overtime_hours ELSE 0 END) AS job1_overtime_hours,
    SUM(CASE WHEN job = 2 THEN regular_hours ELSE 0 END) AS job2_regular_hours,
    SUM(CASE WHEN job = 2 THEN overtime_hours ELSE 0 END) AS job2_overtime_hours
FROM job_hour_breakdown;

How This Works #

Let’s break down the logic step by step:

1. punch_data CTE: First, we calculate the hours per punch and use a window function to compute the cumulative total hours up to each clock-in. This lets us track exactly when the employee crosses the 40-hour regular threshold.
2. job_hour_breakdown CTE: For each punch, we split the hours into regular and overtime:
   • Regular hours: If the total hours before this punch were under 40, we take either the full punch hours (if they don’t push us over 40) or just the remaining hours needed to hit 40. If we’re already over 40, this punch contributes 0 to regular hours.
   • Overtime hours: If the cumulative hours after this punch exceed 40, we calculate how much of this punch is over the threshold by subtracting any overtime already counted in prior punches.
3. Final Aggregation: We sum the regular and overtime hours per job, then use conditional aggregation to pivot the results into the four columns you need.

Verification Against Sample Data #

Let’s cross-check with your dataset:

• Total employee hours: 9 + 8.5 + 9 + 8 + 10 + 10.5 + 5 + 8 = 68 hours. Overtime total is 68 - 40 = 28 hours.
• Job 1 total hours: 33.5. The first 25.5 hours fall under the regular 40-hour limit, so overtime is 33.5 - 25.5 = 8 hours.
• Job 2 total hours: 34.5. The first 14.5 hours are regular, so overtime is 34.5 - 14.5 = 20 hours.

This matches your desired output perfectly!

内容的提问来源于stack exchange，提问作者Yarabimd