sql-server - 如何使用 T-SQL 将时间排名数据转换为 "Timeline"

Question

下面有一组数据

        Agent   Rank    START   STOP    CODE
        Joey    52      11:30   11:45   BRK_Break1
        Joey    53      17:30   17:45   BRK_Break2
        Joey    57      14:15   15:15   BRK_Lunch
        Joey    152     09:40   19:00   CONT_Shift

这是整个数据中的一个人的“状态”。逻辑需要是每个“时间段”都有一行，因此它会根据等级(较低 = 较高优先级)创建一个连续的“时间线”，如下所示

Agent   Start   Stop    Code
Joey    09:40   11:30   CONT_Shift
Joey    11:30   11:45   BRK_Break1
Joey    11:45   14:15   CONT_Shift
Joey    14:15   15:15   BRK_Lunch
Joey    15:15   17:30   CONT_Shift
Joey    17:30   17:45   BRK_Break2
Joey    17:45   19:00   CONT_Shift

有什么想法可以实现吗？理想情况下，我想限制暂存表的使用并通过 CTE 或一些自连接来执行此操作，但不确定从哪里开始？

Answer 1

这是一个非常好的问题，回答起来相当困难。一开始我等着别人来解决，但是因为没有发生，所以我试了一下，我发现了一个错误并再次尝试。

它不是很漂亮，但似乎在 sql 中没有支持该问题的功能。所以sql相当复杂。如果其他人提出不同的更好的解决方案，我会第一个给它加分。

declare @t table (name varchar(10), rank int, start datetime, stop datetime, code varchar(12))
insert @t values ('Joey', 52, '2011-06-21 11:30', '2011-06-21 11:45', 'BRK_Break1')     
insert @t values ('Joey', 53, '2011-06-21 17:30', '2011-06-21 17:45', 'BRK_Break2')         
insert @t values ('Joey', 57, '2011-06-21 14:15', '2011-06-21 15:15', 'BRK_Lunch')     
insert @t values ('Joey',152, '2011-06-21 09:40', '2011-06-21 19:00', 'CONT_Shift')
insert @t values ('Joey',152, '2011-06-22 09:40', '2011-06-22 19:00', 'CONT_Shift')

;with aa as
(
select name, rank, start, 'b' action, code from @t
union all
select name, rank, stop,  'e', code from @t
)
select * from (
select name,start, 
(select min(start) from aa where start > a.start and a.name = name) stop, 
(select code from (select rank() OVER (ORDER BY rank) as rank, code from @t where dateadd(second, 1, a.start) between start and stop and name = a.name) c where rank = 1) code
from aa a
where not exists (select 1 from @t where a.start between start and stop and a.rank > rank and a.name = name)
and exists (select 1 from @t where a.start between start and stop and a.name = name)
) d
where code is not null and 
name = 'Joey'
order by start