mysql - 超过两周的 MySQL 考勤卡加类

Question

我想弄清楚如何计算已经或需要支付的考勤卡账单的加类费。问题是账单涵盖了超过一周的时间，并且查询一次获取了不止一名员工的历史记录。关于如何做到这一点的任何建议，也许是某种案例陈述？

例如，假设在我的员工列表中，一名员工每周工作 39 小时，下周工作 45 小时。该账单将显示 84 小时的工作时间，还需要显示 5 小时的加类时间(而不是 4 小时!)。这需要在以下查询的上下文中完成，该查询处理多个账单和多个员工。

请注意，下面的查询显示了如果计费周期仅为一周，这将如何工作。

select
    username,
    CASE
        WHEN paidOn IS NULL THEN 'Unpaid'
        ELSE paidOn
    END as paid,
    round(sum(TIME_TO_SEC(TIMEDIFF(timeOut, timeIn)))/3600,2) AS hours
    , CASE
       WHEN round(sum(TIME_TO_SEC(TIMEDIFF(timeOut, timeIn)))/3600,2) > 40
           THEN round((sum(TIME_TO_SEC(TIMEDIFF(timeOut, timeIn)))/3600 - 40) * payrate + 40 * payrate,2)
       ELSE
           round(sum(TIME_TO_SEC(TIMEDIFF(timeOut, timeIn)))/3600 * payrate, 2)
    END as pay
from
    timecard
LEFT JOIN
    employees
ON
    employees.userID = timecard.userID
WHERE
    paid != 'd'
GROUP BY
    paidOn, timecard.userID
ORDER BY
    paid DESC
LIMIT 30;

Answer 1

我的理解是，如果计费周期仅为 1 周，则此方法可以正常工作，但如果将其延长至数周，则效果不佳。然后，我将简单地使用您上面的查询作为子查询，然后将您的周汇总在一起。这是我的意思的一个简短示例:

SELECT
   EmployeeId,
   SUM(RegularPayHours) * RegularPayRate,
   SUM(OverTimeHours) * OverTimeRate
FROM
   (SELECT
      EmployeeId,
      DATEPART(week,TimeCardDate) AS [WorkWeek],
      CASE WHEN SUM(HoursWorked) > 40 THEN 40 ELSE SUM(HoursWorked) END AS [RegularPayHours],
      CASE WHEN SUM(HoursWorked) > 40 THEN SUM(HoursWorked) - 40 ELSE 0 END AS [OvertimeHours]
   FROM
      TimeCard
   WHERE
      TimeCardDate BETWEEN StartDate AND EndDate
   GROUP BY
      EmployeeId,
      DATEPART(week,TimeCardDate)
   ) a
WHERE
   WorkWeek IN (1,2)
GROUP BY
   EmployeeId

这将为您提供前两个工作周合并为一个结果，但加类计算是在周级别完成的。您基本上可以使用此方法创建任何 X 周长的自定义支付期。

您可能需要通过设置 DATEFIRST 值来自定义工作周。