First and Last Dates in block of continuous dates

Question

I need to figure out how to capture these blocks in my data. I currently have a query that is capturing the first and last date for each day. The blocks in each day need to be separated. I want to capture the Min and Max date for each block, tech, day and count of blocks (30 min segments) from the first date to the last day. Thanks for answers!

Answer 1

I don't know the offered solution did work for you. But that did not for me. So, I came up with my own using 12c's powerful match_recognize. Let me know if this is what you needed.

To create your sample data.

create table tab as
with inputs(datedate, tech, sched_start, sched_end ) as
(
    select to_date('2020-09-24 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-24 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-24 17:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-24 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-24 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-24 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-24 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-24 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-24 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-25 12:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 12:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 12:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 13:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 13:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 13:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 13:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 14:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 14:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 14:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 14:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 14:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 14:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 15:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-25 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-28 15:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 15:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 16:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 16:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 16:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 16:30:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-29 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 13:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-29 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 13:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-29 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-29 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual
    
)
select * from inputs;

And the solution:

select * from tab
match_recognize
(
    partition by tech
    order by datedate
    measures first(datedate) as min_date,
             last(datedate) as max_date,
             count(*) as total,
             trunc(datedate) as dt
    pattern ( a b*)
    define  b as datedate <= prev(datedate) + numtodsinterval(30, 'minute')
);

Output:

Answer 2

You can see where the groups begin by using lag() and then use a cumulative sum and aggregation. Date/time functions vary among databases, but the idea is:

select min(datedate), max(datedate), tech
from (select t.*,
             sum(case when datedate >= datedate - interval '30' minute then 0 else 1 end) over
                 (partition by tech order by datedate) as grp
      from (select t.*,
                   lag(datedate) over (partition by tech order by datedate) as prev_datedate
            from t
           ) t
     ) t
group by grp, tech;

This assumes that you have a separate series for each "tech", but I don't know if that is really the case.