SQL Server中如何按员工每日获取首次打卡与末次打卡记录？

Got it, let's break down how to solve this problem exactly as you described. The goal is to get one row per employee per day, showing only their first punch-in and last punch-out (ignoring all intermediate punches), while filtering for lunchtype = 'N'.

Approach 1: Aggregation with CASE Statements #

This is the most straightforward method, using basic grouping and conditional aggregation to pull the earliest "I" (PunchIn) and latest "O" (PunchOut) times per employee per day.

WITH FilteredPunches AS (
    SELECT 
        employeeId,
        -- Convert datetime to date to group by day
        CAST(PunchTime AS DATE) AS PunchDate,
        AccessCode,
        PunchTime
    FROM YourTableName
    WHERE lunchtype = 'N'
)
SELECT 
    employeeId,
    PunchDate,
    -- Get the earliest punch-in time for the day
    MIN(CASE WHEN AccessCode = 'I' THEN PunchTime END) AS PunchInTime,
    -- Get the latest punch-out time for the day
    MAX(CASE WHEN AccessCode = 'O' THEN PunchTime END) AS PunchOutTime
FROM FilteredPunches
GROUP BY employeeId, PunchDate
-- Optional: Only include days where both punch-in and punch-out exist
HAVING 
    MIN(CASE WHEN AccessCode = 'I' THEN PunchTime END) IS NOT NULL
    AND MAX(CASE WHEN AccessCode = 'O' THEN PunchTime END) IS NOT NULL;

Key Notes:

• The CTE FilteredPunches first narrows down the data to only records where lunchtype = 'N', and extracts the date part from the punch time for daily grouping.
• MIN(CASE...) grabs the earliest "I" entry (first punch-in), while MAX(CASE...) grabs the latest "O" entry (last punch-out).
• The HAVING clause ensures we only return days where the employee both punched in and out. Remove it if you want to include days with incomplete punches (they'll show NULL for missing times).

Approach 2: Window Functions for Precise Ranking #

If you need more control over selecting specific punches (e.g., handling edge cases where there are multiple "I" entries but you only want the first), window functions like ROW_NUMBER() work great.

WITH RankedPunches AS (
    SELECT 
        employeeId,
        CAST(PunchTime AS DATE) AS PunchDate,
        AccessCode,
        PunchTime,
        -- Rank "I" punches by time ascending (first punch-in = rank 1)
        ROW_NUMBER() OVER (
            PARTITION BY employeeId, CAST(PunchTime AS DATE), AccessCode 
            ORDER BY PunchTime ASC
        ) AS rn_in,
        -- Rank "O" punches by time descending (last punch-out = rank 1)
        ROW_NUMBER() OVER (
            PARTITION BY employeeId, CAST(PunchTime AS DATE), AccessCode 
            ORDER BY PunchTime DESC
        ) AS rn_out
    FROM YourTableName
    WHERE lunchtype = 'N'
)
SELECT DISTINCT
    employeeId,
    PunchDate,
    -- Pull the first punch-in time
    MAX(CASE WHEN AccessCode = 'I' AND rn_in = 1 THEN PunchTime END) 
        OVER (PARTITION BY employeeId, PunchDate) AS PunchInTime,
    -- Pull the last punch-out time
    MAX(CASE WHEN AccessCode = 'O' AND rn_out = 1 THEN PunchTime END) 
        OVER (PARTITION BY employeeId, PunchDate) AS PunchOutTime
FROM RankedPunches
-- Only keep the first punch-in and last punch-out records
WHERE (AccessCode = 'I' AND rn_in = 1) OR (AccessCode = 'O' AND rn_out = 1);

Key Notes:

• We rank each punch type ("I" and "O") per employee per day. For "I", we rank by time ascending so the first punch is rank 1. For "O", we rank by time descending so the last punch is rank 1.
• The final query uses MAX() OVER () to aggregate the selected punch times into a single row per employee per day, and DISTINCT ensures we only get one row.

Important Reminders #

• Replace YourTableName with the actual name of your table.
• Make sure PunchTime is the correct datetime column for your punch records (adjust the name if yours is different).
• Test both queries against your data to see which works best for your specific edge cases (e.g., employees who forgot to punch out, or multiple accidental punches).

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