试图在其他人优先时获得事件的时间

Question

我正在尝试找出如何让某人从事各种任务的时间。有3种不同的级别可以掩盖较低级别的事件。2级隐藏0＆amp;1，1级隐藏0，但仅被2隐藏，并且级别0仅在没有其他可用的情况下才出现

虽然我可以停止在一个下一个级别结束一个事件，但我不知道如何在上一个事件结束后继续进行。如果我对每个人的列表列表，甚至是每分钟的列表，那也适用于我想做的事情。

当前代码和示例表：

declare @test table ([Type] varchar(10), [Date] date, [start] time, [stop] time, [level] int)
insert into @test
values ('Shift',getdate(),'08:00','12:00',0),
       ('WorkEvent',getdate(),'08:10','11:00',1),
       ('Break',getdate(),'10:00','10:10',2)
select *,
case 
      when lead([start]) over( partition by [Date] order by start,[stop]) is null then [stop] -- no more records, don't alter stop time
      when lead([start]) over( partition by [Date] order by start,[stop]) > [stop] then [stop] -- gap in records (for break or other logout), don't alter stop time
      else lead([start]) over( partition by [Date] order by start,[stop]) -- use next records start time as stop time to ensure continuity of work period
      end as [NewStop]
from @test

所需的结果：

Type        Date        start               stop                level
Shift       2019-03-01  08:00:00.0000000    08:10:00.0000000    0
WorkEvent   2019-03-01  08:10:00.0000000    10:00:00.0000000    1
Break       2019-03-01  10:00:00.0000000    10:10:00.0000000    2
WorkEvent   2019-03-01  10:10:00.0000000    11:00:00.0000000    1
Shift       2019-03-01  11:00:00.0000000    12:00:00.0000000    0

我可以使用的其他结果：

08:00 Shift
08:01 Shift
.
.
.
08:09 Shift
08:10 WorkEvent
.
.
.
09:59 WorkEvent
10:00 Break

等。

Answer 1

我敢肯定，这在速度方面不是最佳的，而且我没有用不同的日期对其进行测试。但是无论如何，这似乎是有效的查询

   with valuable_events(dt, timeSt, [level],tp, knd, min_level, seq) as (
        select all_events.*, top_level_x.min_level, ROW_NUMBER() over (order by all_events.dt, all_events.timeSt)
        from
        ( select [Date] as dt, [start] as timeSt, [level], type, 'start' as knd
          from @test T1
            union 
          select [Date] as dt, [stop] as timeSt, [level], type, 'stop' as knd
           from @test T2 
        )all_events
        outer apply (select max([level]) max_level , min([level]) min_level
                     from @test top_prio
                     where all_events.dt = top_prio.Date
                        and all_events.timeSt between top_prio.start and top_prio.stop
                     ) top_level_x
        where all_events.level = top_level_x.max_level
    )
    select iif(evnt.knd='start', evnt.tp, next_evnt.tp) as [Type], 
            evnt.dt as [Date], 
            evnt.timeSt as [start], 
            next_evnt.timeSt as [stop], 
            iif(evnt.knd='start', evnt.level, next_evnt.level) as [Level]
            --, *
    from valuable_events evnt
            join valuable_events next_evnt on evnt.seq = next_evnt.seq-1
    where not (evnt.level = evnt.min_level 
                and evnt.knd = 'stop'
                and next_evnt.knd = 'start')