同日期同EID打卡记录转置：00:00打卡排序异常处理

问题分析 #

原查询的核心问题是分别对TIME_IN和TIME_OUT单独生成行号，导致跨午夜的下班打卡（如00:29:00 AM）会被错误排序，打破了“上班-下班”的配对关系。例如，原查询中按TIME_OUT排序时，午夜时间会被排在最前，对应到错误的上班打卡记录。

解决方案 #

正确的思路是：将每一条“上班-下班”记录视为一个完整的班次，基于**上班打卡的实际时间（日期+时间）**对班次进行排序，统一生成行号，再通过条件聚合实现转置，确保每对打卡记录始终关联。

修正后的SQL查询 #

WITH ShiftOrdered AS (
    SELECT 
        EID,
        ASSOCIATE_NAME,
        TIME_PUNCH_DATE,
        TIME_IN,
        TIME_OUT,
        -- 按上班打卡的完整时间排序，生成班次行号
        ROW_NUMBER() OVER (
            PARTITION BY EID, TIME_PUNCH_DATE 
            ORDER BY CAST(TIME_PUNCH_DATE AS DATETIME) + CAST(TIME_IN AS DATETIME)
        ) AS ShiftRN
    FROM [dbo].[PandaExpress_shortv2]
)
SELECT 
    EID,
    ASSOCIATE_NAME,
    TIME_PUNCH_DATE,
    -- 按班次行号提取对应打卡记录
    MAX(CASE WHEN ShiftRN = 1 THEN TIME_IN END) AS In_Punch1,
    MAX(CASE WHEN ShiftRN = 1 THEN TIME_OUT END) AS Out_Punch1,
    MAX(CASE WHEN ShiftRN = 2 THEN TIME_IN END) AS In_Punch2,
    MAX(CASE WHEN ShiftRN = 2 THEN TIME_OUT END) AS Out_Punch2,
    MAX(CASE WHEN ShiftRN = 3 THEN TIME_IN END) AS In_Punch3,
    MAX(CASE WHEN ShiftRN = 3 THEN TIME_OUT END) AS Out_Punch3
FROM ShiftOrdered
GROUP BY EID, ASSOCIATE_NAME, TIME_PUNCH_DATE;

关键改进点 #

1. 统一班次行号：通过ROW_NUMBER()对每个EID+日期下的完整班次排序，排序依据是上班打卡的完整日期时间（TIME_PUNCH_DATE + TIME_IN），确保跨午夜的班次仍按实际上班时间正确排序。
2. 条件聚合转置：使用CASE语句结合MAX()函数，按班次行号提取对应的上班和下班时间，保证每对打卡记录始终配对。
3. 性能优化：相比原查询的多子查询结构，CTE+条件聚合的方式更高效，适合处理80万行的大数据量。

扩展说明 #

如果存在超过3个班次的情况，只需继续添加对应ShiftRN的CASE语句即可（如MAX(CASE WHEN ShiftRN =4 THEN TIME_IN END) AS In_Punch4）。

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