SQL-запрос для выбора начальной и конечной даты и времени значения с системными таблицами версий

По сути, я хочу использовать системные таблицы версий, чтобы узнать дату начала и окончания всех пользователей, занимающих должность в компании.

Я борюсь с количеством других изменений, внесенных в запись (изменения поля Other, которые создают новую запись с версией). Первоначально я пытался сгруппировать по UserId, CompanyId, Position, а затем взять минимальное SysStartTime и максимальное SysEndTime. Что, на первый взгляд, сработало. Однако это не работает, если позиция возвращается к исходному значению.

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

Сосредоточившись на UserId 1, они были «помощником», затем «менеджером», затем снова «помощником». Я хочу получить дату начала и окончания каждой из этих позиций независимо от того, сколько Other изменений сделано между позициями.

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

Мне нужен запрос, который вернет следующее:

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

Спасибо


sql sql-server temporal-tables
person Ben    schedule 30.12.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Это должно делать то, что вам нужно (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
person Martin Smith    schedule 30.12.2019

arrow_upward
1
arrow_downward

Вы должны использовать LAG для достижения этой цели.

SELECT UserId, CompanyId, Position, StartTime, EndTime
FROM
(
   SELECT DISTINCT 
      cu.UserId, 
      cu.CompanyId, 
      cu.Position,
      LAG(cu.Position) OVER(PARTITION BY cu.UserId,cu.Position ORDER BY (SELECT NULL)) NextPosition
      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
)T 
WHERE Position <> ISNULL(NextPosition,'')

Результат

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
person Never Die    schedule 30.12.2019
comment
Это по-прежнему не дает мне 2 записи «Помощник» для пользователя 1. Он возвращает одну запись со временем начала 13:00 и временем окончания 9999-12-31 23:59. - person Ben; 30.12.2019