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!

To make your example SQL execute correctly, you might make the following changes: a) add `ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI';` before the INSERT statements so that the date strings are interpreted correctly (or explicitly use TO_DATE with that format in the INSERT statements), and, b) change the INSERT statements to use "subcode" instead of "code2" so that they agree with the CREATE TABLE statement. — Brian Camire, Sep 14 '11 at 21:22

score 3 · Accepted Answer · edited Sep 02 '16 at 17:41

3

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

edited Sep 02 '16 at 17:41

Alison R.

4,204
28
33

answered Sep 14 '11 at 18:05

MatBailie

83,401
18
103
137

Thanks for this. The actual query I am working on has a lot more to it than this but this is the part I was stuck on. I appreciate it. – ScottCollier Sep 14 '11 at 19:40

score 3 · Answer 2 · answered Sep 15 '11 at 16:04

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.

Brian Camire · Answer 3 · 2011-09-14T21:23:43.870

Here's another approach:

SELECT
    name,
    min(start_inst) AS start_inst,
    max(end_inst) AS end_inst,
    code,
    subcode
FROM
    (
        SELECT
            A.*,
            COUNT
            (
                CASE WHEN start_inst = previous_end_inst THEN NULL
                ELSE 1
                END
            )
            OVER
            (
                ORDER BY
                    start_inst,
                    name,
                    code,
                    subcode
            ) AS group_number
        FROM
            (
                SELECT
                    name,
                    start_inst,
                    end_inst,
                    LAG
                    (
                      end_inst
                    )
                    OVER
                    (
                        PARTITION BY
                            name,
                            code,
                            subcode
                        ORDER BY
                            start_inst
                    ) AS previous_end_inst,
                    code,
                    subcode
                FROM
                    data
            ) A
        ) B
GROUP BY
    name,
    code,
    subcode,
    group_number
ORDER BY
    group_number

Basically:

For each row, subquery A finds the previous end time for the given name, code, and subcode.
For each row, subquery B calculates the "group number" -- a running count of preceeding rows (in order of start_inst, name, code, and subcode) where the previous end time calculated in Step 1 is not equal to the start time.
The outer query aggregates by group number.

For better or worse, this approach, unlike @stevo's, will create a new "group" if there's a "gap" between the end time of one record and the start time of the next. For example, if you were to create a gap between 12:57 and 13:00 like this...

UPDATE data
SET start_inst = TO_DATE('9/12/2011 13:00', 'MM/DD/YYYY HH24:MI')
WHERE start_inst = TO_DATE('9/12/2011 12:57', 'MM/DD/YYYY HH24:MI');

...the query above would return two rows like this...

NAME                 START_INST       END_INST               CODE    SUBCODE
-------------------- ---------------- ---------------- ---------- ----------
.
.
.
Person1              09/12/2011 12:26 09/12/2011 12:57        161         71
Person1              09/12/2011 13:00 09/12/2011 13:07        161         71
.
.
.

...whereas @stevo's query would return one row like this...

NAME                 START_INST       END_INST               CODE    SUBCODE
-------------------- ---------------- ---------------- ---------- ----------
.
.
.
Person1              12/09/2011 12:26 12/09/2011 13:07        161         71
.
.
.

Hope this helps.

When you say Stevo's solution, you mean the one he sourced from another answer? ;) — MatBailie, Sep 14 '11 at 21:55
The other solution worked better for me in this situation. I am keeping this one for future reference though. Thanks! — ScottCollier, Sep 14 '11 at 21:59
A really clever solution, with very interesting techniques which should be useful in some other similar problems. I think there's a mistake in the code, though, where it says `ORDER BY start_inst, name, code, subcode` in the COUNT it should say `ORDER BY name, code, subcode, start_inst`, thus mimicking the order set by the LAG below. With the example data (modified by @brianc above) there's no difference in the results, but with the real data I'm using for my problem this change has been necessary to obtain correct results. Thank you! — mprost, Jan 09 '14 at 11:50
@mprost, thanks for your comment. The `ORDER BY` in the `COUNT` was deliberate so that we (also) could use it to get the desired order of the final results in the original poster's question. Maybe the problem you are facing is slightly different? To mimic the `LAG`, you could add a `PARTITION BY` to the `COUNT` and move the `name, code, subcode` to there from its `ORDER BY`. You might need to change the `ORDER BY` in the outermost query, though... — Brian Camire, Jan 09 '14 at 14:17
@brianc OK, I know where the difference is, in my data I can have n code-subcodes active at the same time and I have to account for that. If you `INSERT INTO Data VALUES('Person1','9/12/2011 12:00','9/12/2011 12:01',99,99)`, thus creating this situation in the OP's data, you’ll see that 161-50 rows starting at 11:55 and 12:07 are no longer in the same group, despite being consecutive, but my modification to your code place them back in the same group. So you’re right, there’s no mistake in your code, just different data. Thank you for your solution & for taking the time to address my concerns. — mprost, Jan 10 '14 at 10:19

steve godfrey · Answer 4 · 2011-09-14T18:56:29.010

1

adapting desm's query, I think this should work

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,
d.*
FROM
data d
) 
SELECT
  name,
  to_char(MIN(start_inst),'DD/MM/YYYY HH24:MI') start_inst,
  to_char(MAX(end_inst),'DD/MM/YYYY HH24:MI')   end_inst,
  code,
  subcode
FROM
  sequenced_data
GROUP BY
  name,
  code,
  subcode,
  NameSequenceID - NameStateSequenceID
ORDER BY name,start_inst

edited Sep 14 '11 at 18:56

answered Sep 14 '11 at 18:33

steve godfrey

1,234
7
14

Thanks for this. The actual query I am working on has a lot more to it than this but this is the part I was stuck on. I appreciate it. – ScottCollier Sep 14 '11 at 19:39
1

I'd actually recommend against this. There shouldn't be a need to convert the datetime to a string on the server. If the results are being used inside the server, keeping dates allows easier manipulation. If the results ar ebeing used in a client, pass back a data type that can't be mis-understood, uses less space, is more easily formatted if the client interface changes, etc, etc. Unless there are very strong reasons, string formatting is usually best ekpt client side. – MatBailie Sep 14 '11 at 21:53
@dems I'd agree with that, just trying to illustrate the results – steve godfrey Sep 15 '11 at 19:26

score 0 · Answer 5 · edited May 23 '17 at 10:30

0

You can do that with a recursive query (something with CONNECT BY / PRIOR in oracle, IIRC) I did the same thing for Postgres in this thread : Get total time interval from multiple rows if sequence not broken

It might need a bit of reworking to make it fit into the oracle syntax.

edited May 23 '17 at 10:30

Community

1
1

answered Sep 14 '11 at 17:55

wildplasser

43,142
8
66
109

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

5 Answers5

Linked