如何编写SQL将员工打卡记录按天拆分上下班时段并统计工时

原始样本数据 #

ID_Emp| Name    |   Date          
11    |Jonh     |14/05/2014 8:16
11    |Jonh     |14/05/2014 13:35
11    |Jonh     |14/05/2014 17:23
11    |Jonh     |14/05/2014 21:09
12    |Elizabe  |14/05/2014 14:06
12    |Elizabe  |14/05/2014 20:39
12    |Elizabe  |14/05/2014 21:39
12    |Elizabe  |14/05/2014 22:39
13    |Jimmy    |14/05/2014 8:00
13    |Jimmy    |14/05/2014 17:12
13    |Jimmy    |14/05/2014 18:12

需求说明 #

• 按员工ID+日期分组，将当日打卡记录按时间先后排序，依次拆分出首次上班时间TimeIn1、首次下班时间TimeOut1、二次上班TimeIn2、二次下班TimeOut2
• 统计当日有效工时Hours，格式为小时:分钟
• 缺失的打卡位用-填充

期望输出如下：

ID_Emp|Name  |Date          |TimeIn1 |TimeOut1|TimeIn2|TimeOut2|Hours
11  |Jonh    |14/05/2014    |8:16   |13:35     |17:23  |21:09  |5:19
12  |Elizabe |14/05/2014    |14:06  |20:39     |21:39  |22:39  |8:33
13  |Jimmy   |14/05/2014    |8:00   |17:12     |18:12  |  -    |9:12

完整实现代码 #

你当前编写的代码已经实现了相邻打卡匹配为出入对的基础逻辑，只需在此基础上做行转列、工时计算即可，以下是SQL Server环境下的完整实现，其他数据库可对应调整日期函数：

WITH punch_log AS (
    -- 基础数据处理：给每个员工当日的打卡记录排序，匹配相邻打卡对
    SELECT 
        ID_Emp,
        Name,
        CONVERT(DATE, Date) AS work_date,
        CONVERT(TIME, Date) AS punch_time,
        ROW_NUMBER() OVER (PARTITION BY ID_Emp, CONVERT(DATE, Date) ORDER BY Date) AS seq,
        LEAD(CONVERT(TIME, Date)) OVER (PARTITION BY ID_Emp, CONVERT(DATE, Date) ORDER BY Date) AS next_punch
    FROM trnevents
),
punch_pair AS (
    -- 提取有效出入对：奇数位为上班，对应后续匹配值为下班
    SELECT
        ID_Emp,
        Name,
        work_date,
        punch_time AS in_time,
        next_punch AS out_time,
        CEILING(seq/2.0) AS shift_no -- 班次编号：1为第一班次，2为第二班次
    FROM punch_log
    WHERE seq % 2 = 1
),
daily_punch AS (
    -- 行转列聚合为单日一行结果，计算总有效时长
    SELECT
        ID_Emp,
        Name,
        work_date,
        MAX(CASE WHEN shift_no = 1 THEN in_time END) AS TimeIn1,
        MAX(CASE WHEN shift_no = 1 THEN out_time END) AS TimeOut1,
        MAX(CASE WHEN shift_no = 2 THEN in_time END) AS TimeIn2,
        MAX(CASE WHEN shift_no = 2 THEN out_time END) AS TimeOut2,
        -- 累计所有有下班记录的班次的有效分钟数
        SUM(CASE WHEN out_time IS NOT NULL THEN DATEDIFF(MINUTE, in_time, out_time) ELSE 0 END) AS total_min
    FROM punch_pair
    GROUP BY ID_Emp, Name, work_date
)
-- 最终输出，处理格式和缺失值
SELECT
    ID_Emp,
    Name,
    FORMAT(work_date, 'dd/MM/yyyy') AS Date,
    FORMAT(TimeIn1, 'H:mm') AS TimeIn1,
    FORMAT(TimeOut1, 'H:mm') AS TimeOut1,
    ISNULL(FORMAT(TimeIn2, 'H:mm'), '-') AS TimeIn2,
    ISNULL(FORMAT(TimeOut2, 'H:mm'), '-') AS TimeOut2,
    CONCAT(total_min / 60, ':', RIGHT(CONCAT('0', total_min % 60), 2)) AS Hours
FROM daily_punch

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