SQL - Time difference within same column

user_14316819 0
Viewed 243

I need to calculate the time difference in seconds between the events 'PAUSEALL' and 'UNPAUSEALL'.

I haven't been able to make it since i'm facing two issues:

  1. The 'datetime' is in the same columnn for all 'events'
  2. The distance between the identity field called '# queue_stats_id' for the events 'PAUSEALL' and 'UNPAUSEALL' does not follow a sequence.

Actual_SQL_Server_Table

It would help me a lot having the results in the following format:

Results

Thank you in advance!

gaps-and-islands window-functions datetime sql-server sql
1 Answers

You can use window functions. Assuming that pause and unpause events always properly interleave:

select *
from (
    select 
        t.*,
        datediff(second, datetime, 
            min(case when event = 'UNPAUSEALL' then datetime end) over(
                partition by qagent
                order by datetime
                rows between current row and unbounded following
            )
        ) duration_second
    from mytable t
) t
where event = 'PAUSEALL'

The idea is to search the following rows for the closest unpause event, and get the corresponding date.

On the other hand, if there may be consecutive pause or unpause events, then it is different. We need to build groups of adjacent rows first; one option is to use a window count that increments for every pause event:

select t.*, datediff(second, datetime, next_unpause_datetime) duration_second
from (
    select t.*,
        min(case when event = 'UNPAUSEALL' then datetime end) over(partition by qagent, grp) next_unpause_datetime
    from (
        select t.*,
            sum(case when event = 'PAUSEALL' then 1 else 0 end) over(partition by qagent order by datetime) grp
        from mytable t
    ) t
) t
where event = 'PAUSEALL'
user_106767160
Related