SQL & PL/SQL

Oracle Version:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Hi Gurus

I'm stuck with one scenario and need your people help to solve this problem.

I have the following table:

Table Creation and Insertion

drop table ident;

create table ident

(

agreement_id number(5),

ident_pk number(5),

Cov_effective_date date,

Cov_termination_date date

);

-------------------

insert into ident

(

Select 100,1,to_date('2013-01-01','YYYY-MM-DD'), to_date('2013-01-31','YYYY-MM-DD') from dual

union all

Select 200,2,to_date('2013-01-01','YYYY-MM-DD'), to_date('2013-12-31','YYYY-MM-DD') from dual

union all

Select 300,3,to_date('2013-03-01','YYYY-MM-DD'),null from dual

);

Query on table

select * from ident;

Agreement_id           ident_pk               Cov_effective_date                                             Cov_termination_date

------------------------

Drop table ident_dtl;

create table ident_dtl

(

ident_pk number(5),

ident_dtl_pk number(5),

effective_date date,

termination_date date

);

---------------------------------

insert into ident_dtl

(

Select 1 ident_pk,10 ident_dtl_pk,to_date('2013-01-01','YYYY-MM-DD') effective_date, to_date('2013-01-31','YYYY-MM-DD') termination_date from dual--for agreement_id=100

union all

Select 1,11,to_date('2013-01-01','YYYY-MM-DD'), to_date('2013-01-30','YYYY-MM-DD') from dual--for agreement_id=100

union all

Select 2,12,to_date('2013-01-01','YYYY-MM-DD'), to_date('2013-01-30','YYYY-MM-DD') from dual--for agreement_id=200

union all

Select 2,13, to_date('2013-01-01','YYYY-MM-DD'), to_date('2013-01-15','YYYY-MM-DD') from dual--for agreement_id=200

union all

Select 3,14, to_date('2013-01-01','YYYY-MM-DD'), to_date('2013-01-15','YYYY-MM-DD') from dual--for agreement_id=300

union all

Select 3,15, to_date('2013-01-01','YYYY-MM-DD'), to_date('2013-01-15','YYYY-MM-DD') from dual--for agreement_id=300

);

Query on table

Ident_pk       Ident_dtl_pk                         Effective_date                                                     Termination_date         

Desire Result

Agreement_Id                    Ident_pk

200                                        2

300                                        3                                    

------------------------------------

There is a single record entry in ident against agreement_id and Ident_pk and if you can see Ident_pk is also present in child table . my requirement is that at least 1 effective_date and termination_date from child table must be match/equal to parent table that is ident cov_effective_date and cov_termination_date against same Ident_pk column.

for example:

See data in Ident_pk =1,its cov_effective_date = 13-01-01 and its cov_termination_date=13-01-31 and in details table against column Ident_pk  =1  , you can find that at lease 1  combination of date match against  Ident_pk  =1

If you see Ident_pk =2 then you cannot find any effective_date and termination_date against its matching Ident_pk  cov_effective_date and termination_date...

Same as 3

-----------

Summary is that there is date range in ident table against agreement_id and Ident_pk and at least one same date range should be exist in detail table that is ident_dtl against same Ident_pk and if not then show that agreement_id and Ident_pk .

I did this task by using PL/SQL but I think there is a way to do the same task in SQL. Please guide and if you have any questions then please let me know. Thanks

Regards

Shu