sql - Combine rows when the end time of one is the start time of another (Oracle)

Question

I just can't seem to get this query figured out. I need to combine rows of time-consecutive states into a single state.

This question is similar to the question found here except I am working with Oracle 10 not SQL Server: Combine rows when the end time of one is the start time of another

Example data:

name      start_inst         end_inst            code     subcode
Person1 9/12/2011 10:55 9/12/2011 11:49           161   50
Person1 9/12/2011 11:49 9/12/2011 11:55           107   28
Person1 9/12/2011 11:55 9/12/2011 12:07           161   50
Person1 9/12/2011 12:07 9/12/2011 12:26           161   50
Person1 9/12/2011 12:26 9/12/2011 12:57           161   71
Person1 9/12/2011 12:57 9/12/2011 13:07           161   71
Person1 9/12/2011 13:07 9/12/2011 13:20            52   50

And I would like to get the following output:

name       start_inst       end_inst            code     subcode
Person1 9/12/2011 10:55     9/12/2011 11:49     161     50
Person1 9/12/2011 11:49     9/12/2011 11:55     107     28
Person1 9/12/2011 11:55     9/12/2011 12:26     161     50
Person1 9/12/2011 12:26     9/12/2011 13:07     161     71
Person1 9/12/2011 13:07     9/12/2011 13:20     52      50

Here is example SQL:

CREATE TABLE Data (
    name varchar2(132 BYTE) not null,
    start_inst DATE not null,
    end_inst DATE not null,    
code number(3) not null,
subcode number(3) not null
);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 10:55','9/12/2011 11:49',161, 50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 11:49','9/12/2011 11:55',107,28);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 11:55','9/12/2011 12:07',161,50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:07','9/12/2011 12:26',161,50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:26','9/12/2011 12:57',161,71);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:57','9/12/2011 13:07',161,71);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 13:07','9/12/2011 13:20',52,50);

Thanks in advance!

Answer 1

Here is a solution using a recursive query instead of analytic functions (as suggested by @wildplasser):

SELECT   name, code, subcode, MIN(start_inst) AS start_inst, MAX(end_inst) AS end_inst
FROM     (SELECT     name,
                     start_inst,
                     end_inst,
                     code,
                     subcode,
                     MIN(CONNECT_BY_ROOT (start_inst)) AS root_start
          FROM       data d
          CONNECT BY PRIOR name = name 
                 AND PRIOR end_inst = start_inst 
                 AND PRIOR code = code 
                 AND PRIOR subcode = subcode
          GROUP BY   name, start_inst, end_inst, code, subcode)
GROUP BY name, code, subcode, root_start;

The connect by clause in the innermost query causes the data to be returned in a hierarchical fashion. connect_by_root gives us the value at the root of each branch. Because we don't have a good candidate for a start with clause, we'll get all child rows (where end_inst equals another row's start_inst and all other columns are the same) multiple times: once as a root and once (or more) as a branch. Taking the min of the root eliminates these extra rows while giving us a value to group on in the outer query.

In the outer query, we perform another group by to consolidate the rows. The difference is that, in this case, we have root_start there as well to identify which rows are consecutive and therefore need to be consolidated.