Script that consolidates multiple rows of phone data into single rows showing start and end dates

Question

For the below table, I will like to:

i. Consolidate the rows such that for each phone type, the sets of the same phone numbers are consolidated into unique phone numbers with a singular Start and End Date

ii. The consolidated phone number's START DATE is the EARLIEST start date of the set and the consolidated phone number's END DATE is the LATEST end date of the set

iii. Also I want to insure that when certain different phone numbers are sandwiched within a set of the same phone numbers chronologically (represented by the red dash arrows), those phone numbers that are the same are not consolidated into one single row. Rather they are consolidated as separate sets. This is because chronologically, they are now separate sets

The script I have used so far satisfies the first and second requirement, but not the third

SELECT CLAIMANT_ID, PHONE_TYPE, PHONE_NUMBER, 
Min(START_DATE), Max(END_DATE) KEEP (DENSE_RANK FIRST ORDER BY END_DATE DESC NULLS FIRST)
FROM ClaimantData
GROUP BY CLAIMANT_ID, PHONE_TYPE, PHONE_NUMBER

Does anyone know if there's any way to enhance the script (or have a totally different script) that can satisfy the third requirements as well?

The Desired Outcome is as follows:

Sample Data for Insert

CREATE TABLE CLAIMANTDATA (    
CLAIMANT_ID NUMBER(9) NOT NULL,
PHONE_TYPE VARCHAR2(50 BYTE) NOT NULL,
PHONE_NUMBER VARCHAR2(50 BYTE) NULL,
START_DATE DATE NOT NULL,
END_DATE DATE NULL
);

Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Primary',7653030303,to_date('12-JAN-12','DD-MON-RR'),to_date('23-JAN-12','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Primary',7653030303,to_date('09-JAN-12','DD-MON-RR'),to_date('12-JAN-12','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Primary',7653030303,to_date('01-JAN-12','DD-MON-RR'),to_date('09-JAN-12','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('09-MAR-11','DD-MON-RR'),to_date('01-JAN-12','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('22-FEB-11','DD-MON-RR'),to_date('09-MAR-11','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('19-AUG-10','DD-MON-RR'),to_date('22-FEB-11','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('16-AUG-10','DD-MON-RR'),to_date('19-AUG-10','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',2062009876,to_date('14-AUG-10','DD-MON-RR'),to_date('16-AUG-10','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('08-APR-10','DD-MON-RR'),to_date('14-AUG-10','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('06-APR-10','DD-MON-RR'),to_date('08-APR-10','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('08-JUL-09','DD-MON-RR'),to_date('06-APR-10','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('15-JAN-09','DD-MON-RR'),to_date('08-JUL-09','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7650880880,to_date('22-DEC-08','DD-MON-RR'),to_date('15-JAN-09','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('07-AUG-08','DD-MON-RR'),to_date('22-DEC-08','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('06-AUG-08','DD-MON-RR'),to_date('07-AUG-08','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('06-AUG-08','DD-MON-RR'),to_date('06-AUG-08','DD-MON-RR'));
Insert into CLAIMANTDATA (CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,START_DATE,END_DATE) values (504,'Alternate',7651000001,to_date('13-NOV-07','DD-MON-RR'),to_date('28-MAR-08','DD-MON-RR'));

In the desired output. why is the end date for the Primary phone number 21-MAY-12? That doesn't match the inputs; in the inputs the last end_date is 23-JAN-12. What's up with that? — , Jan 10 '21 at 06:53

score 2 · Accepted Answer · answered Jan 10 '21 at 06:41

2

This is called island and gap problem.

You can use analytical function as follows:

Select CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER,
        Min(start_date),
        Max(end_date)
  From
(Select t.*,
       Row_number() over (partition by CLAIMANT_ID, PHONE_TYPE order by start_date) as rn,
       Row_number() over (partition by CLAIMANT_ID, PHONE_TYPE, PHONE_NUMBER order by start_date) as rn_p
   From CLAIMANTDATA t) t
Group by CLAIMANT_ID,PHONE_TYPE,PHONE_NUMBER, rn-rn_p

answered Jan 10 '21 at 06:41

Popeye

35,427
4
10
31

1

Your solution was great! Thank you very much – cmomah Jan 11 '21 at 03:15
please would you mind explaining what "rn-rn_p" in the GROUP BY statement means or does? – cmomah Jan 13 '21 at 14:23
1

row number by CLAIMANT_ID, PHONE_TYPE gives consistent row number and then we are substracting row number by CLAIMANT_ID, PHONE_TYPE, PHONE_NUMBER so it will give different groups for each PHONE_NUMBER island and gap. – Popeye Jan 13 '21 at 14:25

score 1 · Answer 2 · answered Jan 10 '21 at 06:59

In Oracle 12.1 or higher, the easiest way to solve such gaps-and-islands problems is with the match_recognize clause.

select claimant_id, phone_type, phone_number, start_date, end_date
from   claimantdata
match_recognize (
  partition by claimant_id, phone_type
  order     by start_date
  measures  a.phone_number as phone_number,
            a.start_date as start_date, last(end_date) as end_date
  pattern   ( a b* )
  define    b as phone_number = a.phone_number and start_date = prev(end_date)
);

@cmomah - Popeye's solution is perfectly good, and it has two advantages: it works in older versions of Oracle (before version 12.1) and it uses standard SQL. Apparently `match_recognize` is now also in the SQL standard, but only Oracle implements it. No worries then! — , Jan 11 '21 at 04:42

Script that consolidates multiple rows of phone data into single rows showing start and end dates

2 Answers2