CODEWITHSUNDEEP
sqlsql-serversql-server-2012
SeanC
SeanC

Reputation: 15923

trying to get times for events when others take precedence

I am trying to work out how to get the times someone is working on various tasks. There are 3 different levels that can mask events occurring at a lower level. Level 2 hides 0 & 1, level 1 hides 0, but is hidden by 2, and level 0 appears only when nothing else is available

While I can get the stop of one event to end at the next level, I don't know how to get it to continue after the previous event has finished. If what I have for each person is a list of times, and an even for each minute, that would also work for what I want to do.

Current code, and example table:

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

desired results:

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

other result which I could work with:

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

etc.

Upvotes: 2

Views: 64

Answers (1)

Renat
Renat

Reputation: 8962

I'm sure this one is not optimal in terms of speed, and I haven't tested it with different dates. But anyway here is a query which seems to work

   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')

Upvotes: 1

Related Questions