SQL 2008下员工考勤进出配对及周统计方案咨询

Hey there! Let's work through this attendance data pairing and weekly aggregation problem that's fully compatible with SQL Server 2008. I'll break this down into actionable steps with clear SQL code and explanations.

The solution needs to handle three key requirements:

• Pair each AttendanceTypeID=2 (check-out) record with the most recent, unpaired AttendanceTypeID=1 (check-in) record
• Calculate the duration for each valid check-in/check-out pair
• Aggregate total durations per employee, grouped by weeks starting on Sunday and ending on Saturday

First, let's assume your attendance table has a structure like this (adjust column names/types to match your actual schema):

-- Example attendance table (modify to fit your data)
CREATE TABLE Attendance (
    EmployeeID INT,
    AttendanceTime DATETIME,
    AttendanceTypeID INT -- 1 = Check-in, 2 = Check-out
);

Here's the complete query that meets all your requirements:

WITH RankedAttendance AS (
    SELECT 
        EmployeeID,
        AttendanceTime,
        AttendanceTypeID,
        -- Assign a sequential rank to each employee's attendance records by time
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY AttendanceTime) AS RowNum
    FROM Attendance
),
PairedAttendance AS (
    SELECT 
        ra1.EmployeeID,
        ra1.AttendanceTime AS CheckInTime,
        ra2.AttendanceTime AS CheckOutTime,
        -- Calculate duration in minutes (convert to hours if needed)
        DATEDIFF(MINUTE, ra1.AttendanceTime, ra2.AttendanceTime) AS SessionDurationMinutes
    FROM RankedAttendance ra1
    INNER JOIN RankedAttendance ra2 
        ON ra1.EmployeeID = ra2.EmployeeID
        AND ra1.RowNum = ra2.RowNum - 1
        AND ra1.AttendanceTypeID = 1 -- Ensure we're pairing a check-in first
        AND ra2.AttendanceTypeID = 2 -- Followed immediately by a check-out
)
-- Aggregate by employee and Sunday-starting week
SELECT 
    EmployeeID,
    -- Format the week range as "YYYY-MM-DD 至 YYYY-MM-DD" (Sunday to Saturday)
    CONVERT(VARCHAR, DATEADD(DAY, 1 - DATEPART(WEEKDAY, CheckInTime), CheckInTime), 120) + ' 至 ' +
    CONVERT(VARCHAR, DATEADD(DAY, 7 - DATEPART(WEEKDAY, CheckInTime), CheckInTime), 120) AS WeekRange,
    SUM(SessionDurationMinutes) AS TotalWeeklyMinutes,
    -- Optional: Convert total minutes to hours for readability
    ROUND(SUM(SessionDurationMinutes) / 60.0, 2) AS TotalWeeklyHours
FROM PairedAttendance
GROUP BY 
    EmployeeID,
    DATEADD(DAY, 1 - DATEPART(WEEKDAY, CheckInTime), CheckInTime)
ORDER BY 
    EmployeeID,
    DATEADD(DAY, 1 - DATEPART(WEEKDAY, CheckInTime), CheckInTime);

• SQL Server 2008 Support: All functions used (ROW_NUMBER(), DATEDIFF(), DATEADD(), DATEPART()) are fully supported in SQL Server 2008—no newer features like LAG()/LEAD() are needed here.
• Valid Pairing Logic: The RankedAttendance CTE sorts each employee's records by time, then we pair adjacent records where a check-in is immediately followed by a check-out. This ensures each check-out is matched to the most recent check-in, even if there are multiple pairs per day.
• Sunday-Starting Weeks: DATEPART(WEEKDAY, ...) returns 1 for Sunday in SQL Server's default setting. Using DATEADD(DAY, 1 - DATEPART(...), CheckInTime) gives us the Sunday of the week the check-in occurred, and adding 6 days gives us the corresponding Saturday.
• Handling Edge Cases:
  • Cross-day shifts (e.g., check-in on Monday night, check-out on Tuesday morning) are handled automatically—duration calculation works across dates.
  • Unpaired records (e.g., missing check-out or check-in) are excluded from the final aggregation. If you need to track these exceptions, you can add an additional query to identify records not present in PairedAttendance.

内容的提问来源于stack exchange，提问作者R. Miller