Scott Collier Scott Collier - 3 months ago 8
SQL Question

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

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

Maybe this? (I don't have a SQL machine to run it on)

WITH
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY name                ORDER BY start_inst) NameSequenceID,
    ROW_NUMBER() OVER (PARTITION BY name, code, subcode ORDER BY start_inst) NameStateSequenceID,
    *
  FROM
    data
)
SELECT
  name,
  MIN(start_inst) start_inst,
  MAX(end_inst)   end_inst,
  code,
  subcode
FROM
  sequenced_data
GROUP BY
  name,
  code,
  subcode,
  NameSequenceID - NameStateSequenceID
Comments