Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL question (Oracle 11g)
PL/SQL question [message #563748] Thu, 16 August 2012 14:12 Go to next message
sanka
Messages: 1
Registered: August 2012
Location: Boston
Junior Member
create table my_tmp(
   id           number,
   missing_dt   date);
   
insert into my_tmp (id, missing_dt) values (111, sysdate-15);
insert into my_tmp (id, missing_dt) values (111, sysdate-14);
insert into my_tmp (id, missing_dt) values (111, sysdate-13);

insert into my_tmp (id, missing_dt) values (111, sysdate-10);

insert into my_tmp (id, missing_dt) values (111, sysdate-7);
insert into my_tmp (id, missing_dt) values (111, sysdate-6);

insert into my_tmp (id, missing_dt) values (222, sysdate-12);
insert into my_tmp (id, missing_dt) values (222, sysdate-9);

commit;

select id, trunc(missing_dt) from my_tmp order by id, missing_dt;

1              111         8/1/2012
2              111         8/2/2012
3              111         8/3/2012
4              111         8/6/2012
5              111         8/9/2012
6              111         8/10/2012
7              222         8/4/2012
8              222         8/7/2012

Requirement: Show ids and missing_dt (range if the dates are contiguous; same date if they are not)

Expected Result Set;
id     missing_dt_start   missing_dt_end
-----------------------------------------------------------------
111    8/1/2012           8/3/2012            -- range
111    8/6/2012           8/6/2012            -- individual date
111    8/9/2012           8/10/2012           -- range
222    8/4/2012           8/4/2012            -- individual date
222    8/7/2012           8/7/2012            -- individual date

-- drop table my_tmp;

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 16 August 2012 14:30] by Moderator

Report message to a moderator

Re: PL/SQL question [message #563753 is a reply to message #563748] Thu, 16 August 2012 14:37 Go to previous message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Assuming id, trunc(missing_dt) combination is unique:

SQL> with t as (
  2             select  id,
  3                     trunc(missing_dt) missing_dt,
  4                     trunc(missing_dt) - row_number() over(order by trunc(missing_dt)) grp
  5               from  my_tmp
  6            )
  7  select  id,
  8          min(missing_dt) missing_dt_start,
  9          max(missing_dt) missing_dt_end
 10    from  t
 11    group by id,
 12             grp
 13    order by id,
 14             grp
 15  /

        ID MISSING_D MISSING_D
---------- --------- ---------
       111 01-AUG-12 03-AUG-12
       111 06-AUG-12 06-AUG-12
       111 09-AUG-12 10-AUG-12
       222 04-AUG-12 04-AUG-12
       222 07-AUG-12 07-AUG-12

SQL> 


SY.
Previous Topic: getting error in cimple cursor
Next Topic: nullif and zero_divide
Goto Forum:
  


Current Time: Wed Sep 17 21:20:36 CDT 2014

Total time taken to generate the page: 0.08847 seconds