无进出标识多班次考勤打卡时间计算SQL方案问询

Got it, let's tackle this head-on. The core challenge here is mapping unlabeled punch records to IN/OUT pairs while fixing edge cases like duplicate data, multiple daily punches, night shifts, and missed punches that plagued your original row-number approach.

Here's an optimized, cleaner solution using modern window functions (instead of messy recursive CTEs) that addresses all your pain points:

-- Define your missed punch threshold (adjust as needed)
DECLARE @MissedPunchThreshold INT = 20;

-- Step 1: Remove duplicate punch records (fixes system-generated duplicates)
WITH DeduplicatedAttendance AS (
    SELECT DISTINCT employeecode, punchdate
    FROM attendance_trial -- Replace with your dbo.AttLog table
    -- Add WHERE clause here to filter specific dates/employees if needed
),
-- Step 2: Assign sequence numbers and fetch next punch time for pairing
PunchSequences AS (
    SELECT 
        employeecode,
        punchdate,
        ROW_NUMBER() OVER (PARTITION BY employeecode ORDER BY punchdate) AS PunchSeq,
        LEAD(punchdate) OVER (PARTITION BY employeecode ORDER BY punchdate) AS NextPunchDate,
        -- Handle night shifts: map late punches to the correct workday (customize logic to match your policy)
        CASE 
            WHEN DATEPART(HOUR, punchdate) >= 18 THEN CAST(DATEADD(DAY, 1, punchdate) AS DATE)
            ELSE CAST(punchdate AS DATE)
        END AS AttendanceDate
    FROM DeduplicatedAttendance
),
-- Step 3: Pair IN/OUT records and flag missed punches
PunchPairs AS (
    -- Pair odd-numbered punches (IN) with even-numbered (OUT)
    SELECT 
        employeecode,
        AttendanceDate,
        punchdate AS Time_In,
        -- Mark OUT as null if gap exceeds threshold (missed punch)
        CASE 
            WHEN DATEDIFF(HOUR, punchdate, NextPunchDate) > @MissedPunchThreshold THEN NULL
            ELSE NextPunchDate
        END AS Time_Out,
        -- Calculate hours worked (use minutes for precision)
        DATEDIFF(MINUTE, punchdate, NextPunchDate) / 60.0 AS HoursWorked
    FROM PunchSequences
    WHERE PunchSeq % 2 = 1

    -- Capture unpaired final punches (e.g., employee forgot to clock out)
    UNION ALL
    SELECT 
        employeecode,
        AttendanceDate,
        punchdate AS Time_In,
        NULL AS Time_Out,
        NULL AS HoursWorked
    FROM PunchSequences
    WHERE PunchSeq % 2 = 0 
      AND LEAD(punchdate) OVER (PARTITION BY employeecode ORDER BY punchdate) IS NULL
)
-- Step 4: Aggregate daily totals per employee
SELECT 
    employeecode,
    AttendanceDate,
    MIN(Time_In) AS FirstLogin,
    MAX(Time_Out) AS LastLogout,
    SUM(HoursWorked) AS TotalHoursWorked
FROM PunchPairs
GROUP BY employeecode, AttendanceDate
ORDER BY employeecode, AttendanceDate;

Key Improvements & Customization Tips #

• Duplication Fix: The DeduplicatedAttendance CTE removes exact duplicate punch records (common in faulty attendance systems). For near-duplicates (e.g., punches within 5 minutes), replace DISTINCT with a grouping by rounded time.
• Simpler Pairing: Uses LEAD() instead of recursion to link each punch to the next one—far more efficient and easier to debug.
• Night Shift Support: The AttendanceDate logic maps late punches to the correct workday. Adjust the hour threshold (18 in the example) to match your company's night shift policy.
• Missed Punch Handling: The threshold check flags gaps larger than your set hours as missed punches, marking the OUT time as null.
• Unpaired Punches: Captures cases where an employee has an odd number of punches (e.g., forgot to clock out) and includes them in the final output.

内容的提问来源于stack exchange，提问作者Ganesha Moorthy Arunachalam