SQL query to select the start and end datetime of a value with system versioned tables

Question

Basically, I want to use system versioned tables to find out the start and end date all users held a position within a company.

I'm struggling with the amount of other changes made to the record (Other field changes that create a new versioned record). I originally tried to Group By UserId, CompanyId, Position and then take the min SysStartTime and max SysEndTime. Which at first glance did work. However it does not work if a position is changed back to its original value.

SELECT DISTINCT 
    cu.UserId, 
    cu.CompanyId, 
    cu.Position,
    MIN(cu.SysStartTime) AS StartTime,
    MAX(cu.SysEndTime) AS EndTime
FROM dbo.CompanyUser FOR SYSTEM_TIME ALL cu
GROUP BY cu.UserId, cu.CompanyId, cu.Position

Focusing on UserId 1, they were an 'Assistant', then a 'Manager', then back to an 'Assistant' again. I want to get the start and end date of each of these positions reguardless of how many Other changes are made between positions.

UserId     CompanyId     Position      Other     SysStartTime             SysEndTime
--------   -----------   -----------   -------   ----------------------   ---------------------
1          1             Assistant     A         2019-12-01 13:00:00      2019-12-01 14:00:00
2          1             Manager       A         2019-12-01 13:00:00      2019-12-01 20:00:00
1          1             Assistant     B         2019-12-01 14:00:00      2019-12-01 17:00:00
1          1             Manager       A         2019-12-01 17:00:00      2019-12-01 20:00:00
2          1             Executive     A         2019-12-01 20:00:00      9999-12-31 23:59:59
3          1             CEO           A         2019-12-01 13:00:00      9999-12-31 23:59:59
1          1             Assistant     A         2019-12-01 20:00:00      9999-12-31 23:59:59

I want a query that will return the following:

UserId     CompanyId     Position      SysStartTime             SysEndTime
--------   -----------   -----------   ----------------------   ---------------------
1          1             Assistant     2019-12-01 13:00:00      2019-12-01 17:00:00
2          1             Manager       2019-12-01 13:00:00      2019-12-01 20:00:00
1          1             Manager       2019-12-01 17:00:00      2019-12-01 20:00:00
2          1             Executive     2019-12-01 20:00:00      9999-12-31 23:59:59
3          1             CEO           2019-12-01 13:00:00      9999-12-31 23:59:59
1          1             Assistant     2019-12-01 20:00:00      9999-12-31 23:59:59

Thanks

Answer 1

This should do what you need (Fiddle).

WITH T
     AS (SELECT *,
                LAG(Position) OVER (PARTITION BY UserId ORDER BY SysStartTime) AS PrevPosition
         FROM dbo.CompanyUser FOR SYSTEM_TIME ALL cu)
SELECT UserId,
       CompanyId,
       Position,
       Other,
       SysStartTime,
       SysEndTime = LEAD(SysStartTime, 1, SysEndTime) OVER (PARTITION BY UserId ORDER BY SysStartTime)
FROM   T
WHERE  EXISTS (SELECT PrevPosition
               EXCEPT
               SELECT Position)
ORDER  BY UserId,
          SysStartTime