asked on

Oracle sql

I have a table with following data

ID TYPE EFF_DATE END_DATE
---------- ---- ------------------------- -------------------------
123 01 01-JAN-05 31-JAN-05
123 02 15-JAN-05 31-MAR-05
1234 01 01-JAN-05 31-JAN-05
1234 02 01-FEB-05 31-MAR-05
12345 01 01-JUN-05 31-JUL-05
12345 02 25-JUL-05 31-OCT-05

I would like to select ID, eff_date, end_date, calc_end_date where type =01.

Calc_end_date = If a member has an type segment(01) from 1/1/05 - 1/31/05 and a another segment(type 02) from 1/15/05 - 1/31/05
we only want to use the segment 1/1/05 - 1/14/05 for type 01

I would like to have out put as follows

ID TYPE EFF_DATE END_DATE calc_end_date
---------- ---- ------------------------- ------------------------- ---------------------
123 01 01-JAN-05 31-JAN-05 14-JAN-2005
1234 01 01-JAN-05 31-JAN-05 31-JAN-2005
12345 01 01-JUN-05 31-JUL-05 24-JUL-2005

Please help me

I am just adding my initial scripts for creat table and isert data

drop table tmp_date_expo;
create table tmp_date_expo
(id varchar2(10),
type varchar2(3),
eff_date date,
end_date date);

insert into tmp_date_expo values('123','01','01-JAN-2005', '31-JAN-2005');

insert into tmp_date_expo values('123','02','15-JAN-2005', '31-MAR-2005');

insert into tmp_date_expo values('1234','01','01-JAN-2005', '31-JAN-2005');

insert into tmp_date_expo values('1234','02','01-FEB-2005', '31-MAR-2005');

insert into tmp_date_expo values('12345','01','01-JUN-2005', '31-JUL-2005');
insert into tmp_date_expo values('12345','02','25-JUL-2005', '31-OCT-2005');

Christoffer Swanström

I don't quite understand the logic for changing the dates, could you try to rephrase it? Under which circumstances does eff_date and end_date get changed and according to which rules? How is calc_end_date calculated?

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

Can there be types other than 01 and 02? Can there be more than one type that is the same for a single id (e.g. id-123 type-01, eff_date,end_date and also id-123 type-01, a different eff_date and/or a different end_date)?

ronythom

ASKER

Thanks you guys for helpfull responses.It's not yet solved I am working on actual data

@awkingoo
Both cases are possible. More than 2 types and different date range segmants for each id.

@johanntagle

I wonder whether this query will effect the performance. Because in my actual table there is milllions of records.

ronythom

ASKER

Experts.. Please help me.. a

awking00

Can you provide some sample data that would cover all of those scenarios (i.e. multiple types, multiple ids with same types, with overlapping dates and with no overlapping dates) and your desired output?

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johanntagle

Can you provide the complete listof scenarios? Like if there are other types, will they always be compared to type 01 or can you compare type 02 with type 03? Or do you query one type at a time, meaning for all type 01, you check if there is a higher type that overlaps the effectivity dates?

My initial query should work okay on large data provided that id and type have proper indexes.

Mark Geerlings

Oops! I think there is an extra ")" character in the query in my previous comment.

The line:
and t2.eff_date between t1.eff_date and t1.end_date)),t1.END_DATE)

should be like this:
and t2.eff_date between t1.eff_date and t1.end_date),t1.END_DATE)

ronythom

ASKER

With all your suggestions works for me. Sorry for the delayed reply.