SQL Server多班次登录登出时间计算方案技术问询

I get it—you're trying to split employee shifts when there's a 5+ hour gap between a logout and the next login, plus calculate on-floor/off-floor times and flag missing entry/exit records. Let’s walk through a solution that handles all these requirements, including your edge cases like cross-day shifts and missing logout entries.

Step-by-Step Explanation & Full SQL Code #

We’ll use common table expressions (CTEs) to break down the problem into manageable parts:

WITH Logs AS (
    -- Step 1: Clean and standardize the raw log data
    SELECT 
        [Emp ID] AS EmpID,
        [Time],
        -- Normalize access type (handle inconsistent spacing in area names)
        CASE 
            WHEN [Area Of Access] LIKE '%IN' THEN 'IN'
            WHEN [Area Of Access] LIKE '%OUT' THEN 'OUT'
            ELSE 'UNKNOWN'
        END AS AccessType,
        -- Assign row numbers to sort logs chronologically per employee
        ROW_NUMBER() OVER (PARTITION BY [Emp ID] ORDER BY [Time]) AS RowNum
    FROM [dbo].[emplogindetails]
    -- Filter out any invalid records that aren't IN/OUT
    WHERE [Area Of Access] LIKE '%IN' OR [Area Of Access] LIKE '%OUT'
),
ShiftGroups AS (
    -- Step 2: Group logs into shifts based on the 5-hour gap rule
    SELECT 
        EmpID,
        [Time],
        AccessType,
        RowNum,
        -- Calculate shift ID: increment when an IN follows an OUT with >5hr gap
        SUM(CASE 
            WHEN AccessType = 'IN' 
                 AND LAG(AccessType) OVER (PARTITION BY EmpID ORDER BY RowNum) = 'OUT'
                 AND DATEDIFF(MINUTE, LAG([Time]) OVER (PARTITION BY EmpID ORDER BY RowNum), [Time]) > 300 -- 5hrs = 300 mins
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY EmpID ORDER BY RowNum) + 1 AS ShiftID
    FROM Logs
),
ShiftSummary AS (
    -- Step 3: Calculate core metrics per shift
    SELECT 
        EmpID,
        ShiftID,
        MIN([Time]) AS ShiftStartTime,
        MAX([Time]) AS ShiftEndTime,
        -- Count total IN/OUT entries
        SUM(CASE WHEN AccessType = 'IN' THEN 1 ELSE 0 END) AS Incount,
        SUM(CASE WHEN AccessType = 'OUT' THEN 1 ELSE 0 END) AS OutCount,
        -- Calculate total on-floor time (sum of IN to OUT durations)
        SUM(CASE 
            WHEN AccessType = 'IN' 
            THEN DATEDIFF(SECOND, [Time], LEAD([Time]) OVER (PARTITION BY EmpID, ShiftID ORDER BY RowNum))
            ELSE 0 
        END) AS OnFloorSeconds,
        -- Calculate total off-floor time (sum of OUT to next IN durations within the same shift)
        SUM(CASE 
            WHEN AccessType = 'OUT' 
            AND LEAD(AccessType) OVER (PARTITION BY EmpID, ShiftID ORDER BY RowNum) = 'IN'
            THEN DATEDIFF(SECOND, [Time], LEAD([Time]) OVER (PARTITION BY EmpID, ShiftID ORDER BY RowNum))
            ELSE 0 
        END) AS OffFloorSeconds,
        -- Determine Tailgate status:
        -- 1 = Shift ends with an IN (missing logout), -1 = Shift starts with an OUT (missing login), 0 = Complete shift
        CASE 
            WHEN MAX(CASE WHEN RowNum = (SELECT MAX(RowNum) FROM ShiftGroups sg2 WHERE sg2.EmpID = sg.EmpID AND sg2.ShiftID = sg.ShiftID) THEN AccessType END) = 'IN' THEN 1
            WHEN MIN(CASE WHEN RowNum = (SELECT MIN(RowNum) FROM ShiftGroups sg2 WHERE sg2.EmpID = sg.EmpID AND sg2.ShiftID = sg.ShiftID) THEN AccessType END) = 'OUT' THEN -1
            ELSE 0
        END AS Tailgate
    FROM ShiftGroups sg
    GROUP BY EmpID, ShiftID
)
-- Step 4: Format the final output to match your expected structure
SELECT 
    CONVERT(VARCHAR(10), ShiftStartTime, 101) AS ShiftDate,
    ShiftStartTime,
    ShiftEndTime,
    -- Format total shift time (OnFloor + OffFloor)
    CONVERT(VARCHAR, DATEADD(SECOND, OnFloorSeconds + OffFloorSeconds, 0), 108) AS Total_Time,
    -- Format on-floor time
    CONVERT(VARCHAR, DATEADD(SECOND, OnFloorSeconds, 0), 108) AS OnFloor,
    -- Format off-floor time
    CONVERT(VARCHAR, DATEADD(SECOND, OffFloorSeconds, 0), 108) AS OffFloor,
    EmpID,
    Incount,
    OutCount,
    Tailgate
FROM ShiftSummary
ORDER BY EmpID, ShiftStartTime;

Key Features Explained #

1. Shift Splitting: The ShiftGroups CTE uses a running sum to assign unique shift IDs. It increments the ID only when an IN record follows an OUT record with a gap of more than 5 hours.
2. Duration Calculations: We use DATEDIFF to calculate seconds for precision, then convert to a readable time format in the final select.
3. Tailgate Flagging: Checks the first and last record of each shift to identify missing IN or OUT entries.
4. Handling Inconsistent Data: The Logs CTE normalizes access types even if there are spacing differences in the Area Of Access field (like K4F White Rm IN vs K4FWhite Rm OUT).

Testing with Your Sample Data #

For your EmpID=5 test data, this query will group all records into a single shift since all gaps between OUT and subsequent IN are less than 5 hours. If you add an IN record for EmpID=5 at 2017-08-03 11:30:00 (6 hours after the last OUT at 06:00:00), it will be split into a new shift automatically.

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