oracle sql group by change in time valid from valid to

Question

I have following historical data regarding tickets status changes over time, thing I'm interested in is to have results with VALFROM-VALTO

The source data

ticket_ID, change-time        , queue
--------------------------------------
001      , 2018-01-01 00:00:00, queue1
001      , 2018-01-01 00:01:00, queue1
001      , 2018-01-01 00:03:00, queue2
001      , 2018-01-01 00:04:00, queue1
001      , 2018-01-01 00:05:00, queue3

The target data

ticket_ID, valfrom            , valto              , queue
-----------------------------------------------------------
001      , 2018-01-01 00:00:00, 2018-01-01 00:02:59, queue1
001      , 2018-01-01 00:03:00, 2018-01-01 00:03:59, queue2
001      , 2018-01-01 00:04:00, 2018-01-01 00:04:59, queue1
001      , 2018-01-01 00:05:00, 2999-12-31 23:59:59, queue3

I think this can be done using ROW_NUMBER function with OVER. Any useful advice is more than welcome.

Answer 1

You can do it in various ways, here I used lag() to eliminate rows, where previous change_time is the same as current and used lead() to show next value as valto:

^{SQLFiddle demo}

select id, queue, ct valfrom, 
       nvl(lead(ct) over (order by ct) - interval '1' second, 
           date '3000-01-01' - interval '1' second) valto
  from (select id, ct, queue, lag(queue) over (order by ct) lq from source)
  where lq is null or lq <> queue