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?

Where is the END for Hamlet? Also your data isn't properly normalized. Each play name must have a unique ID, which you should've used in this table instead of the name. — Racil Hilan, Apr 13 '18 at 10:53
from the documentation:LAG { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] ) } OVER ([ query_partition_clause ] order_by_clause) LEAD { ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] ) } OVER ([ query_partition_clause ] order_by_clause) — nabuchodonossor, Apr 13 '18 at 10:54
Yes, but it gives the wrong idea. You should at least include the play ID column. As you can see, the answers you got are grouping by the play name, which is a very bad idea, but you didn't leave them any choice. — Racil Hilan, Apr 13 '18 at 11:04
How should the Break be used in all of this? For example what if the Break for Romeo and Juliet was over 60 minutes? If that were the case then Romeo and Juliet was also under an hour. I think we need more information before giving an answer. If the break is not being considered in the calculation then no problem but if it needs to be taken into account then the duration of the break in each case will need to be known. It also changes the code required. — DrHouseofSQL, Apr 13 '18 at 12:09
@RacilHilan Thanks for the feedback, I'll be posting full case scenario next time. Meanwhile you can check the answer to the problem posted by Barbaros Özhan. Cheers — Doniu, Apr 13 '18 at 13:47

Jay Shankar Gupta · Answer 1 · 2018-04-13T11:55:22.923

2

SELECT
    Play_name,
    CASE WHEN  EXTRACT(HOUR FROM MAX(CASE WHEN Status = 'END' THEN Date END) - MAX(CASE WHEN Status = 'Start' THEN Date END)) < 1
         THEN 'NO'
    ELSE 'YES'
    END AS under_hour
FROM TABLENAME
GROUP BY Play_name

edited Apr 13 '18 at 11:55

answered Apr 13 '18 at 10:56

Jay Shankar Gupta

5,918
1
10
27

You picked up on the exact output the OP wants +1. – Tim Biegeleisen Apr 13 '18 at 11:03

Barbaros Özhan · Accepted Answer · 2018-04-13T12:43:02.800

2

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

edited Apr 13 '18 at 12:43

answered Apr 13 '18 at 12:03

Barbaros Özhan

59,113
10
31
55

Great Answer, exactly what I was looking for. Thank you! – Doniu Apr 13 '18 at 13:45
@Doniu Thank you too very much :) – Barbaros Özhan Apr 13 '18 at 14:58

Tim Biegeleisen · Answer 3 · 2018-04-13T11:01:37.400

We can try aggregating by play name and using a pivot to bring the start and end time into a single record. Then, assert that the difference between these two timestamps is strictly less than one hour.

SELECT
    Play_name
FROM yourTable
GROUP BY Play_name
HAVING
    EXTRACT(HOUR FROM MAX(CASE WHEN Status = 'END' THEN Date END) -
        MAX(CASE WHEN Status = 'Start' THEN Date END)) < 1

Edit: It seems that you want all plays reported, with a yes/no status. In this case, you can use the logic in my HAVING clause in a CASE statement. Or, just go with the answer by @Jay above.

score 0 · Answer 4 · answered Apr 13 '18 at 12:00

0

I think you don't to check the Status value, all you need to do is get the time difference between min and max date of the each play_name record.

select  Play_name,CASE when (max(Date)-min(date)) > 1 then 'Yes' else 'No' end under_hour from table

answered Apr 13 '18 at 12:00

Pelin

936
5
12

Time difference under 1h for group of records

4 Answers4