mysql - 计算日期范围内每个工作日的工作时间

Question

我有一个包含以下字段的表 timeandattandance

TAId int(11) NOT NULL AUTO_INCRMENT,
PostId int(11) NOT NULL,
PositionId int(11) NOT NULL,
创建者 int(11) NOT NULL,
修改者 int(11) NOT NULL,
CreatedDate 日期时间 NOT NULL,
ModifiedDate 日期时间 NOT NULL，
TimeIn 日期时间 NOT NULL,
超时日期时间默认为 NULL，
TimeBilled tinyint(1) NOT NULL DEFAULT '0',
DeviceId varchar(50) 默认为 NULL，
UserId int(11) 默认为 NULL，
oldid varchar(45) 默认为 NULL，
FromCallIn tinyint(1) 默认 '0'

我需要获取每个用户每天的工作时间，并按日期范围之间的周结束日期分组。也就是说我已经得到了一个日期范围。首先，我需要找出 timein 将在此日期范围之间，然后我需要获取所有一周的结束日期，然后每周我需要计算每天的工作时间。

此外，在计算工作时间时，我需要检查 timein 和 timeout 之间的差异是否超过一天，那么这些工作时间将分开两天而不是一天。

我知道这有点复杂，如果需要更多解释，请告诉我。

Answer 1

以下是一种可能方法的示例/演示:

SELECT r.dt + INTERVAL 7-DAYOFWEEK(r.dt) DAY AS week_ending
     , t.userid
     , r.dt
     , SUM(TIMESTAMPDIFF(SECOND
             ,GREATEST(r.dt,t.timein)
             ,LEAST(r.dt+INTERVAL 1 DAY,t.timeout)
           )
       )/3600 AS hours_worked
  FROM ( SELECT '2014-09-28' AS rb, '2014-10-11' AS re) dr
  JOIN ( SELECT DATE(i.timein) AS dt FROM mytable i 
          UNION
         SELECT DATE(o.timeout) FROM mytable o
       ) r
    ON r.dt BETWEEN DATE(dr.rb) AND DATE(dr.re)
  JOIN mytable t
    ON t.timein  < r.dt+INTERVAL 1 DAY
   AND t.timeout > r.dt
 GROUP BY t.userid, r.dt
 ORDER BY week_ending, t.userid, r.dt

注意:week_ending 返回工作日期之后的星期六的日期。

日期范围在内联 View dr(日期范围)中指定，范围开始日期为列rb，范围结束日期为列re。此示例显示两周范围，从 2014 年 9 月 28 日星期日开始，持续两周，包括 2014 年 10 月 11 日星期六的工作时间。 (re 的值可以从 rb 导出为整数天数，以获得 14 个完整天，`rb + INTERVAL 13 DAY)

仅报告这些日期或这些日期之间的工作时间。在给定日期没有任何工作“时间”的给定用户 ID 将不会返回该日期的行。 (可以轻松调整查询以返回范围内所有日期的所有员工的行，并返回零。)

如果没有包含所有日期值的“cal”日历表，我们可以从表本身的范围内获得不同的日期值列表；对于表中的大量或多行来说，这可能是相对昂贵的操作。这不会返回未出现在 timein 或 timeout 列中的日期值。 (如果工作时间超过 24 小时，例如从星期一开始到星期三结束。并且没有其他行在星期三超时或超时，则当天的 24 小时工作时间将被省略...这可能是极端的情况。日历表中所有可能的日期值的不同列表可以避免这个问题。)

<小时/>

跟进

从您的评论来看，您似乎需要在不同的日期列表和不同的用户列表之间进行叉积，然后与工作时间表进行外部联接。

   JOIN ( distinct_list_of_dates_r ) r
     ON ( r_in_specified_week )

  CROSS
   JOIN ( distinct_list_of_userid_u ) u
   LEFT

   JOIN mytable t
     ON ( t_userid_matches_u )
    AND ( t_times_matches_r  )

然后GROUP BY来自u的用户ID，而不是来自t的用户ID。您可能希望将 hours_worked 返回的 NULL 值替换为 0。MySQL IFNULL 函数对此很方便，IFNULL(expr,0) 是 CASE WHEN expr IS NULL THEN 0 ELSE expr END 的简写。如果您想要整周的总小时数，而不是按个别日期计算，请对 week_ending 表达式执行 GROUP BY，而不是对单个日期执行。