I have a table

person_id start_month end_month
1 200301 200303
1 200302 200303
1 200303 200304
1 200306 200308


For the first 3 records, the end_month of the previous record is less
than the start_month of the next record. I want to write a SQL to
combine them and give me the following result

person_id start_month end_month
1 200301 200304
1 200306 200308

I can easily do it in PL/SQL but wanted to do it in SQL. Please help.

Thanks
PK

On 2005-08-26, premgoel@gmail.com <premgoel@gmail.com> wrote:
>
>
> I have a table
>
> person_id start_month end_month
> 1 200301 200303
> 1 200302 200303
> 1 200303 200304
> 1 200306 200308
>
>
> For the first 3 records, the end_month of the previous record is less
> than the start_month of the next record. I want to write a SQL to
> combine them and give me the following result
>
> person_id start_month end_month
> 1 200301 200304
> 1 200306 200308


What makes the first 3 records be the first three records?




--
Rene Nyffenegger
http://www.adp-gmbh.ch/

This is basically to track the period for which the patient was taking
a drug. The patients fills a prescription before the previous one
finishes. So the patient is on the same drug withourt interruption.

premgoel@gmail.com wrote:
> This is basically to track the period for which the patient was taking
> a drug. The patients fills a prescription before the previous one
> finishes. So the patient is on the same drug withourt interruption.

Patients often miss days, often stop taking meds, and often overlap
prescriptions.

It may be your business rules ... but it isn't reality.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

On 2005-08-26, premgoel@gmail.com <premgoel@gmail.com> wrote:
> This is basically to track the period for which the patient was taking
> a drug. The patients fills a prescription before the previous one
> finishes. So the patient is on the same drug withourt interruption.

Look into the analytical function lag().

hth
Rene


--
Rene Nyffenegger
http://www.adp-gmbh.ch/

The following query should provide the required output:

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

premgoel@gmail.com wrote:
> I have a table
>
> person_id start_month end_month
> 1 200301 200303
> 1 200302 200303
> 1 200303 200304
> 1 200306 200308
>
>
> For the first 3 records, the end_month of the previous record is less
> than the start_month of the next record. I want to write a SQL to
> combine them and give me the following result
>
> person_id start_month end_month
> 1 200301 200304
> 1 200306 200308
>
> I can easily do it in PL/SQL but wanted to do it in SQL. Please help.
>
> Thanks
> PK

ruskie wrote:
> The following query should provide the required output:
>
> 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

Excuse me but ... SELECT DISTINCT in a GROUP BY?

If a GROUP BY has repeating values they should be dealt
with using GROUP_ID() as in the example at www.psoug.org
click on Morgan's Library
click on GROUP BY
scroll down to "Group By With Repeating Values"

I suspect you will find it far more efficient.

HTH
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

"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_mo nth,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