Hi Tom,
I have following requirement,
create table sim_history
(sim varchar2(30),
imsi varchar2(30),
start_date date,
end_date date
)
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('11/21/2007 12:41:58', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/15/2010 15:14:10', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('04/15/2010 15:14:10', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('07/24/2012 19:21:18', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('B', '20', TO_DATE('07/24/2012 19:21:18', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('07/25/2012 08:39:08', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('07/25/2012 08:39:08', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/20/2013 13:11:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('11/20/2013 13:11:03', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/20/2013 13:37:57', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('11/20/2013 13:37:57', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/20/2013 13:38:01', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('11/20/2013 13:38:01', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/25/2015 21:00:47', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('05/25/2015 21:00:47', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/25/2015 21:04:02', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('A', '10', TO_DATE('05/25/2015 21:04:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/25/2015 21:04:03', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('C', '30', TO_DATE('05/25/2015 21:04:03', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/25/2015 21:08:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('C', '30', TO_DATE('05/25/2015 21:08:52', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/08/2015 10:57:02', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('C', '30', TO_DATE('06/08/2015 10:57:02', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/08/2015 11:07:32', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('C', '30', TO_DATE('06/08/2015 11:07:32', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/08/2015 11:44:22', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE, END_DATE)
Values
('C', '30', TO_DATE('06/08/2015 11:44:22', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/08/2015 12:32:35', 'MM/DD/YYYY HH24:MI:SS'));
Insert into SIM_HISTORY
(SIM, IMSI, START_DATE)
Values
('C', '30', TO_DATE('06/08/2015 12:32:35', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
now data set looks like below
sim imsi start_date end_date
A 10 11/21/2007 12:41 4/15/2010 15:14
A 10 4/15/2010 15:14 7/24/2012 19:21
B 20 7/24/2012 19:21 7/25/2012 8:39
A 10 7/25/2012 8:39 11/20/2013 13:11
A 10 11/20/2013 13:11 11/20/2013 13:37
A 10 11/20/2013 13:37 11/20/2013 13:38
A 10 11/20/2013 13:38 5/25/2015 21:00
A 10 5/25/2015 21:00 5/25/2015 21:04
A 10 5/25/2015 21:04 5/25/2015 21:04
C 30 5/25/2015 21:04 5/25/2015 21:08
C 30 5/25/2015 21:08 6/8/2015 10:57
C 30 6/8/2015 10:57 6/8/2015 11:07
C 30 6/8/2015 11:07 6/8/2015 11:44
C 30 6/8/2015 11:44 6/8/2015 12:32
C 30 6/8/2015 12:32
pls note - end time is always start time of the next row
required output is
sim imsi start_date end_date
A 10 11/21/2007 12:41 7/24/2012 19:21
B 20 7/24/2012 19:21 7/25/2012 8:39
A 10 7/25/2012 8:39 5/25/2015 21:04
C 30 5/25/2015 21:04
Question - Is it possible to generate above output using single query (analytics function or any other way) other than using detail procedure
Thanks
Upendra