"select distinct t1.person_id,
min(t2.start_month) as start_date,
max(t2.end_month) as end_date
from temp t1, temp t2
where t1.start_month <= t2.end_month
and t2.start_month <= t1.end_month
group by t1.person_id, t1.start_month, t1.end_month
RH"
This query will give the correct results with the given data. However,
it doesn't take into account that more than two records can combine to
form one. I basically used a hierarchical query to connect the records
based on their start and stop times. From there, I could figure out
the latest month that each record extends to, and then combine them by
returning the earliest month with each maximum latest month.
I'm pasting a SQL*Plus script I wrote to illustrate what I did. It'll
prompt for an html file to spool to. Also note that it drops and
creates a table called person, so as with any script, please do view it
before running. As always, I appreciate any comments/suggestions.
Regards,
Arun
set echo on
set markup html on
set pagesize 0
spool &html_filename_to_output_to
drop table person;
create table person(person_pk number(11) not null,
person_id number(11) not null,
start_month date,
end_month date);
alter table person add constraint person_primary
primary key(person_pk);
--
--
- insert statements
--
insert into person(person_pk,person_id,start_month,end_month) values
(1,1,to_date('01-JAN-03','DD-MON-YY'),to_date('01-MAR-03','DD-MON-YY'));
insert into person(person_pk,person_id,start_month,end_month) values
(2,1,to_date('01-FEB-03','DD-MON-YY'),to_date('01-MAR-03','DD-MON-YY'));
insert into person(person_pk,person_id,start_month,end_month) values
(3,1,to_date('01-MAR-03','DD-MON-YY'),to_date('01-APR-03','DD-MON-YY'));
insert into person(person_pk,person_id,start_month,end_month) values
(4,1,to_date('01-JUN-03','DD-MON-YY'),to_date('01-AUG-03','DD-MON-YY'));
insert into person(person_pk,person_id,start_month,end_month) values
(5,1,to_date('01-JUL-03','DD-MON-YY'),to_date('01-SEP-03','DD-MON-YY'));
--
insert into person(person_pk,person_id,start_month,end_month) values
(6,1,to_date('01-APR-03','DD-MON-YY'),to_date('01-MAY-03','DD-MON-YY'));
insert into person(person_pk,person_id,start_month,end_month) values
(7,1,to_date('01-FEB-03','DD-MON-YY'),to_date('01-JUN-03','DD-MON-YY'));
--
--
select * from person
order by start_month,end_month;
- one approach proposed by ruskie
select distinct t1.person_id,
min(t2.start_month) as start_date,
max(t2.end_month) as end_date
from person t1, person t2
where t1.start_month <= t2.end_month
and t2.start_month <= t1.end_month
group by t1.person_id, t1.start_month, t1.end_month;
- create a view based on a hierarchical query to identify the time
intervals
- that can extend the other
- ex. JAN-MAR -> MAR-APR -> APR-MAY
- JAN-MAR -> FEB-JUN
create or replace view v_schedule_path as
select schedule.*,sys_connect_by_path(t2_person_pk,'/') time_path,
decode(instr(sys_connect_by_path(t2_person_pk,'/'),'/',2),0,
substr(sys_connect_by_path(t2_person_pk,'/'),
instr(sys_connect_by_path(t2_person_pk,'/'),'/',1)+1),
substr(sys_connect_by_path(t2_person_pk,'/'),
instr(sys_connect_by_path(t2_person_pk,'/'),'/',1)+1,
instr(sys_connect_by_path(t2_person_pk,'/'),'/',2)
- instr(sys_connect_by_path(t2_person_pk,'/'),'/',1) -1)) root_branch,
level depth from
(
select t1.person_pk t1_person_pk,t1.person_id
t1_person_id,t1.start_month t1_start_month,t1.end_month t1_end_month,
t2.person_pk t2_person_pk,
t2.person_id t2_person_id,
t2.start_month t2_start_month,
t2.end_month t2_end_month
from person t1,person t2
where t1.person_id = t2.person_id
-- and t2.start_month >= t1.start_month
and t2.start_month <= t1.end_month
and t2.end_month > t1.end_month
) schedule
connect by prior t1_person_pk = t2_person_pk
and t1_person_id = t2_person_id;
select * from v_schedule_path
order by t1_person_id,t1_start_month,t2_end_month;
- simply identify the end months for each root
create or replace view v_schedule_path2 as
select v_schedule_path.*,person.end_month root_end_month
from v_schedule_path,person
where v_schedule_path.t1_person_id = person.person_id
and v_schedule_path.root_branch = person.person_pk
/
select * from v_schedule_path2
order by t1_person_id,t1_start_month,t1_end_month,root_end_month
/
- for each interval, identify the latest ending month it can extend to
create or replace view v_schedule_path3 as
select
t1_person_id,t1_person_pk,t1_start_month,t1_end_month,max(root_end_month)
over (partition by t1_start_month,t1_end_month) max_end_month
from v_schedule_path2;
select * from v_schedule_path3
order by max_end_month,t1_start_month
/
--
- get the earliest starting month for each latest ending month
--
select t1_person_id,min(t1_start_month) start_month,max_end_month
from v_schedule_path3
group by t1_person_id,max_end_month
order by t1_person_id,min(t1_start_month)
/
select * from v$version;
show user
set markup html off
spool off
quit
Received on Wed Sep 07 2005 - 10:26:41 CDT
