troubleshooting Question

Oracle sql

Avatar of ronythom
ronythom asked on
Oracle Database
10 Comments2 Solutions419 ViewsLast Modified:
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');
Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros