CODEWITHSUNDEEP
sqloracle12c
Ali Adnan
Ali Adnan

Reputation: 86

How to find a gap between two dates in a same column based on group chnage

I have sample log table as below

enter image description here

I want to find a difference between each "END" and next "Start" TYPE. as below

enter image description here

I have setup a SQLFiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Logs ( id, DateTime, Type ) AS
  SELECT 9000, TO_DATE('2018-03-01 09:00:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03/10 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-11 11:00:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03/20 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03-21 10:05:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03/25 09:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-26 10:15:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03/27 12:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9002, TO_DATE('2018-03-28 14:30:27','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9002, TO_DATE('2018-04-05 15:02:56','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL;

Please guide me how can I achieve this

EDIT 1:

As "wolfrevokcats" perfectly produce the result I mentioned, I have little modification in my Out Result I want to add the GAP Result in Next Start ID like below picture

enter image description here

By Modifying code provided by "wolfrevokcats" I am able to get the result

Full Working Code is below for reference

WITH logs AS ( 
  SELECT 9000 id, TO_DATE('2018-03-01 09:00:00','RRRR-MM-DD HH24:MI:SS') AS datetime, 'Start' AS type FROM DUAL UNION ALL 
  SELECT 9000, TO_DATE('2018-03/10 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL 
  SELECT 9001, TO_DATE('2018-03-11 11:00:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL 
  SELECT 9001, TO_DATE('2018-03/20 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL 
  SELECT 9000, TO_DATE('2018-03-21 10:05:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL 
  SELECT 9000, TO_DATE('2018-03/25 09:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL 
  SELECT 9001, TO_DATE('2018-03-26 10:15:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL 
  SELECT 9001, TO_DATE('2018-03/27 12:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL 
  SELECT 9002, TO_DATE('2018-03-28 14:30:27','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL 
  SELECT 9002, TO_DATE('2018-04-05 15:02:56','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL 
)  
, t as ( 
  select id, type, lag(type) over(order by datetime) prev_type 
         , datetime time_end, lag(datetime) over(order by datetime) prev_time_end
  from logs l 
) 
select t.id, t.time_end, t.prev_time_end 
  , trunc(time_end)  - trunc(t.prev_time_end) difference 
from t  
where nvl(prev_type,'END') = 'END' and type='Start';

Upvotes: 2

Views: 79

Answers (3)

wolfrevokcats
wolfrevokcats

Reputation: 2100

Using a simple lead would do:

WITH logs AS (
  SELECT 9000 id, TO_DATE('2018-03-01 09:00:00','RRRR-MM-DD HH24:MI:SS') AS datetime, 'Start' AS type FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03-10 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-10 11:00:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-20 10:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03-20 10:05:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9000, TO_DATE('2018-03-25 09:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-25 10:15:00','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9001, TO_DATE('2018-03-26 12:00:00','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL UNION ALL
  SELECT 9002, TO_DATE('2018-03-26 14:30:27','RRRR-MM-DD HH24:MI:SS'), 'Start' FROM DUAL UNION ALL
  SELECT 9002, TO_DATE('2018-04-05 15:02:56','RRRR-MM-DD HH24:MI:SS'), 'END'   FROM DUAL
) 
, t as (
  select id, type, lead(type) over(order by datetime) next_type
         , datetime time_end, lead(datetime) over(order by datetime) next_time_start
  from logs l
)
select t.id, t.time_end, t.next_time_start
  , trunc(t.next_time_start) - trunc(time_end) difference
from t 
where nvl(next_type,'Start') = 'Start' and type='END'

ID    TIME_END             NEXT_TIME_START      DIFFERENCE  
----- -------------------- -------------------- ----------- 
9000  2018-03-10 10:00:00  2018-03-10 11:00:00  0           
9001  2018-03-20 10:00:00  2018-03-20 10:05:00  0           
9000  2018-03-25 09:00:00  2018-03-25 10:15:00  0           
9001  2018-03-26 12:00:00  2018-03-26 14:30:27  0           
9002  2018-04-05 15:02:56

Note that all the differences in your SQLFiddle data are less that 1 day, so the query shows only zeros.

Upvotes: 3

Andomar
Andomar

Reputation: 238196

You can use row_number to assign an increasing number to each start and end within the same document. Then you can join start and end together:

with    numbered as
        (
        select  id
        ,       datetime
        ,       type
        ,       row_number() over (partition by id, type order by datetime) rn
        from    Logs
        )
select  a.id
,       b.datetime as TIME_END
,       a.datetime as NEXT_TIME_START
,       b.datetime - a.datetime as DIFF
from    numbered a
left join
        numbered b
on      a.id = b.id
        and a.rn = b.rn  -- Match the Nth Start to the Nth END
        and b.type = 'END'
where   a.type = 'Start'

This method doesn't use the type column at all. It relies on there being a matching END for every Start. It should work for overlapping periods.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Assuming that your starts and ends exactly line up (as in your sample data), you can enumerate them and then aggregate:

select id, min(datetime_disp) as start_time, max(datetime_disp) as end_time,
       trunc(max(datetime_disp) - min(datetime_disp)) as diff
from (select l.*,
             row_number() over (partition by id, type order by datetime_disp) as seqnum
      from logs l
     ) l
group by id, seqnum;

Note that this assumes that the values line up (never two starts in a row, for instance). If this can happen, then ask another question and explain how to handle those situations.

Upvotes: 1

Related Questions