Grouping data in a time interval

Question

I have a requirement to get the start and end times in a state where val = 'Y'and col_val < 5.

I have a table called temp_vvv, like

date_time             |     val    |  col_val
-------------------------------------------------------------------------
03/06/2014 08:58:00         Y          4
03/06/2014 08:59:00         Y          3
03/06/2014 09:00:00         Y          1
03/06/2014 09:01:00         Y          0
03/06/2014 09:01:30         Y          0
03/06/2014 09:02:00         Y          2
03/06/2014 09:02:30         Y          5
03/06/2014 09:03:00         Y         20
03/06/2014 09:03:30         Y         50
03/06/2014 09:04:00         Y         55
03/06/2014 09:04:30         Y         60
03/06/2014 09:05:00         Y         10
03/06/2014 09:05:30         Y          4
03/06/2014 09:06:00         Y          2
03/06/2014 09:06:30         Y          0
03/06/2014 09:07:00         Y          0
03/06/2014 09:07:30         Y          1
03/06/2014 09:08:00         N          5
03/06/2014 09:08:30         N          0 

I expect a result like

  start_date_time       |     end_date_time
 --------------------------------------------------
 03/06/2014 08:58:00         03/06/2014 09:02:00
 03/06/2014 09:05:30         03/06/2014 09:07:30

How to get this output from select query

Please help me.

EDIT

The value col_val <5 begins with '03/06/2014 08:58:00'and ends with '03/06/2014 09:02:00'. So the first entry start_date_timeis equal '03/06/2014 08:58:00'and end_date_timeequal '03/06/2014 09:02:00', etc.

How to do it?

Answer 1

You can use the analytic function ROW_NUMBER()to group records based on COLand COL_VAL.

,

SELECT MIN(date_time) start_date_time, MAX(date_Time) end_date_time
FROM(
    SELECT date_time, val, col_val, CASE WHEN val = 'Y' AND col_val < 5 THEN 1 ELSE 0 END col_val_limit,
                  ROW_NUMBER() OVER (ORDER BY DATE_TIME) - 
          ROW_NUMBER() OVER (PARTITION BY CASE WHEN val = 'Y' AND col_val < 5 THEN 1 ELSE 0 END ORDER BY DATE_tIME) grp
    FROM    temp_vw
    )
WHERE  col_val_limit = 1
GROUP BY grp
ORDER BY start_date_time;

Answer 2

select
  min(date_time),
  max(date_time)
from (
  select
    date_time,
    sum(new_group) over (order by date_time) group_
  from (
    select
      date_time,
      val,
      col_val,
      case when
        case when lag(val    ) over (order by date_time) = 'Y' and
                  lag(col_val) over (order by date_time) <  5  then
             1
        else 0 end 
        !=
        case when val = 'Y' and
                  col_val < 5 then
             1
        else 0 end
      then
        1
      else
        0
      end              new_group
    from
      tq84_t
  )
  where
    val = 'Y' and col_val < 5
)
group by group_;

SQL Fiddle.

Answer 3

Not tested, but the idea is to identify the group to which each line refers to the beginning of the group. The start time is determined by obtaining the minimum effective line (val = 'Y' and col_val = '5'), where there is no invalid line between them. Finally, the rows are grouped by start time, and the end time is retrieved usingMAX

SELECT min_time start_date_time, MAX(date_time) end_date_time
FROM (
    SELECT
    date_time,
    (
        SELECT MIN(date_time)
        FROM temp_vvv t2
        WHERE val = 'Y' AND col_val < 5
        AND NOT EXISTS (SELECT 1 FROM temp_vvv t3
            WHERE t3.date_time BETWEEN t2.date_time AND t1.date_time
            AND (t3.val <> 'Y' OR t3.col_val >= 5)
        )
    ) min_time
    FROM temp_vvv t1
    WHERE t1.val = 'Y'
    AND t1.col_val < 5
) GROUP BY min_time

http://sqlfiddle.com/#!4/64545/8