Time difference under 1h for group of records

Question

Table looks like this

    Play_name       Status  Date
  1 Romeo & juliet  Start   23.01.2018 16:30:00
  2 Romeo & juliet  Break   23.01.2018 17:15:00
  3 Romeo & juliet  END     23.01.2018 18:30:00
  4 Hamlet          Start   25.01.2018 15:45:00
  5 Hamlet          END     25.01.2018 16:40:00

etc

I need to return play_name with column under_hour with yes for all the plays that lasted shorter than 1h ( from Start to End ).

Results should look like that:

   Play_name        under_hour
 1 Romeo & Juliet   NO
 2 Hamlet           Yes

How can i achieve that?

Answer 1

LAG function may be used with contribution of SIGN and DECODE :

select rownum rn, Play_name, 
       decode(sign(Finish_Time - Start_Time - (1/24)),-1,'Yes','NO') under_hour
  from
  (
   select Play_name, 
          lag(play_Date,1,null) over (partition by play_name order by play_Date) Start_Time, 
          Play_Date Finish_Time, Status
     from Plays
    where Status in ('Start','END')
    order by Play_Date
   ) 
where Status = 'END';

demo