Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Upendra.

Asked: January 20, 2017 - 6:51 am UTC

Last updated: January 22, 2017 - 11:54 am UTC

Version: oracle 11g R2

Viewed 1000+ times

You Asked

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


and Connor said...

I made some assumptions about what you define as a boundary condition, but here you go

SQL> select spanning_grp,
  2         min(sim),
  3         min(imsi),
  4         min(start_date) date_from,
  5         max(end_Date) date_to
  6  from (
  7  select p.*,
  8         max(lo_val) over ( order by end_Date ) as spanning_grp
  9  from (
 10  select t.*,
 11         case
 12         when nvl(lag(SIM||to_char(end_date,'yyyymmddhh24miss')) over ( order by end_date ),end_date) != SIM||to_char(start_date,'yyyymmddhh24miss')
 13         then row_number() over ( order by end_date) end lo_val
 14  from SIM_HISTORY t
 15  ) p
 16  )
 17  group by spanning_grp
 18  order by 4;

SPANNING_GRP MIN(SIM)                       MIN(IMSI)                      DATE_FROM           DATE_TO
------------ ------------------------------ ------------------------------ ------------------- -------------------
           1 A                              10                             21/11/2007 12:41:58 24/07/2012 19:21:18
           3 B                              20                             24/07/2012 19:21:18 25/07/2012 08:39:08
           4 A                              10                             25/07/2012 08:39:08 25/05/2015 21:04:03
          10 C                              30                             25/05/2015 21:04:03 08/06/2015 12:32:35


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The last row in the output is incorrect

Rajeshwaran, Jeyabal, January 22, 2017 - 6:42 pm UTC

The last row in the output is incorrect and it is not matching up with the expected values.

demo@ORA12C> select sim,grp,min(imsi),min(start_date),
  2            max(end_date) keep(dense_rank
  3                first order by end_date desc nulls first) end_date
  4  from (
  5  select sim,imsi,start_date,end_date,
  6            last_value( rn ignore nulls ) over( order by end_date ) grp
  7  from (
  8  select t.* ,
  9    case when lag(end_date) over( order by end_date ) is null or
 10              ( lag(end_date) over(order by end_date) <>
 11                start_date or
 12                lag(sim) over(order by end_date) <> sim ) then
 13                row_number() over(order by end_date) end rn
 14  from sim_history t
 15      )
 16      )
 17  group by sim,grp
 18  order by grp ;

SIM               GRP MIN(IMSI)                      MIN(START_DATE)         END_DATE
---------- ---------- ------------------------------ ----------------------- -----------------------
A                   1 10                             21-NOV-2007 12:41:58 PM 24-JUL-2012 07:21:18 PM
B                   3 20                             24-JUL-2012 07:21:18 PM 25-JUL-2012 08:39:08 AM
A                   4 10                             25-JUL-2012 08:39:08 AM 25-MAY-2015 09:04:03 PM
C                  10 30                             25-MAY-2015 09:04:03 PM

demo@ORA12C>

super

Upendra Pasqual, January 23, 2017 - 8:11 am UTC

Thanks a lot guys, it works. really appreciate the help.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.