如何利用System Versioned Tables获取用户各职位分段的起止时间

Got it, I see exactly where your original approach fell short—grouping directly by UserId, CompanyId, and Position merges all identical roles for a user together, even when they’re separated by a different position in between. That’s why User 1’s reverted Assistant role wasn’t showing up as a distinct segment. Let’s fix this with a window function-based solution that tracks consecutive position periods.

Sample Data #

Correct SQL Query #

WITH RankedPositions AS (
    SELECT 
        UserId,
        CompanyId,
        Position,
        SysStartTime,
        SysEndTime,
        -- Create a unique group ID for each consecutive position segment
        SUM(CASE WHEN prev_position = Position THEN 0 ELSE 1 END) 
            OVER (PARTITION BY UserId, CompanyId ORDER BY SysStartTime) AS position_group
    FROM (
        SELECT 
            UserId,
            CompanyId,
            Position,
            SysStartTime,
            SysEndTime,
            -- Fetch the previous position for the same user/company to detect changes
            LAG(Position) OVER (PARTITION BY UserId, CompanyId ORDER BY SysStartTime) AS prev_position
        FROM dbo.CompanyUser FOR SYSTEM_TIME ALL
    ) AS position_history
)
SELECT 
    UserId,
    CompanyId,
    Position,
    MIN(SysStartTime) AS SysStartTime,
    MAX(SysEndTime) AS SysEndTime
FROM RankedPositions
GROUP BY UserId, CompanyId, Position, position_group
ORDER BY UserId, CompanyId, SysStartTime;

How This Works #

1. Inner Subquery (position_history): Uses the LAG() window function to pull the position from the immediately preceding version for each user/company, ordered by SysStartTime. This lets us check if the current position is the same as the last one.
2. CTE (RankedPositions): Creates a position_group value. Every time the position changes from the previous row, we increment the group ID. This groups together all consecutive rows where the user held the same position (even if other fields like Other were modified).
3. Final SELECT: Groups by UserId, CompanyId, Position, and position_group—this ensures we only merge identical positions that are consecutive, not all identical positions across the user’s history. We then take the minimum start time and maximum end time for each group to get the full duration of that position segment.

Expected Output #

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