SQL Server考勤系统：匹配首次上班与末次下班打卡需求

现有EmpLogs2表数据 #

Id      Pin     Time                    DeviceMode
---------------------------------------------------
23086   7756    2023-01-06 07:27:00.000 IN
23237   7756    2023-01-06 09:10:00.000 OUT
23241   7756    2023-01-06 09:11:00.000 OUT
23246   7756    2023-01-06 09:12:00.000 OUT
23248   7756    2023-01-06 09:13:00.000 OUT
23301   7756    2023-01-06 10:20:00.000 OUT

期望处理后的数据格式 #

Id      Pin     Time In                     DeviceMode     Time Out                 
---------------------------------------------------------------------------------------
23086   7756    2023-01-06 07:27:00.000         IN         2023-01-06 10:20:00.000  OUT
23237   7756    2023-01-06 09:10:00.000         OUT
23241   7756    2023-01-06 09:11:00.000         OUT
23246   7756    2023-01-06 09:12:00.000         OUT
23248   7756    2023-01-06 09:13:00.000         OUT
23301   7756    

尝试的SQL代码（未实现需求） #

from (
select  *, 
            in_mins = CASE  
            WHEN    DeviceMode in ('IN')
            AND     LEAD(DeviceMode) OVER (PARTITION BY Pin ORDER BY [Time]) in ('OUT')
            THEN    LEAD([Time]) OVER (PARTITION BY Pin ORDER BY [Time] )
                            ELSE    0
                            END
             --out_mins= CASE  WHEN    DeviceMode in ('OUT')
    --                        AND     LEAD(DeviceMode) OVER (PARTITION BY Pin ORDER BY [Time]) in ('OUT')
    --                        THEN    DATEDIFF(MINUTE, 
    --                                         [Time], 
    --                                         LEAD([Time]) OVER (PARTITION BY Pin ORDER BY [Time]))
    --                        ELSE    0
    --                        END
    from    EmpLogs2
    ) t 
    order by time

解决方案SQL #

WITH LastOutRecord AS (
    SELECT 
        Pin,
        MAX(CASE WHEN DeviceMode = 'OUT' THEN [Time] END) AS LastOutTime,
        MAX(CASE WHEN DeviceMode = 'OUT' THEN Id END) AS LastOutId
    FROM EmpLogs2
    GROUP BY Pin
)
SELECT 
    el.Id,
    el.Pin,
    -- 控制Time In列的显示：IN记录、非最后一条的OUT记录显示时间，最后一条OUT记录空值
    CASE 
        WHEN el.DeviceMode = 'IN' OR el.Id <> lor.LastOutId THEN el.[Time] 
        ELSE NULL 
    END AS [Time In],
    -- 控制DeviceMode列的显示：IN记录、非最后一条的OUT记录显示模式，最后一条OUT记录空值
    CASE 
        WHEN el.DeviceMode = 'IN' OR el.Id <> lor.LastOutId THEN el.DeviceMode 
        ELSE NULL 
    END AS DeviceMode,
    -- IN记录显示对应的最后OUT时间，其余为空
    CASE WHEN el.DeviceMode = 'IN' THEN lor.LastOutTime ELSE NULL END AS [Time Out],
    -- IN记录显示OUT模式，其余为空
    CASE WHEN el.DeviceMode = 'IN' THEN 'OUT' ELSE NULL END AS [Out DeviceMode]
FROM EmpLogs2 el
LEFT JOIN LastOutRecord lor ON el.Pin = lor.Pin
ORDER BY el.[Time];

逻辑说明 #

1. LastOutRecord CTE：按Pin分组，提取每个员工最后一条OUT记录的时间和ID，作为IN记录匹配的下班时间依据。
2. 主查询：
   • 对IN记录，展示打卡时间、模式，同时匹配最后一条OUT的时间和模式
   • 对中间的OUT记录，正常展示打卡时间和模式
   • 对最后一条OUT记录，仅保留Id和Pin，其余列留空
   • 最终按打卡时间排序

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