Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Find records with overlapping begin and end dates

676422Mar 17 2009 — edited Mar 17 2009
Hi all,

i have a table with begin and end dates columns. I need to find overlapping records with few matching column values.

table: MG_AUTH_AGNT
-----
ID
MGATH_clnt
MGATH_beg_DT
MGATH_END_DT
MGATH_SERV_GRP
MGATH_STAT
MGATH_TYP
MGATH_NHIC_ERR_CD
-----
I need to find records with overlapping dates.

Sample data
-----

if client has two records with same MGATH_SERV_GRP ,MGATH_STAT,MGATH_TYP and begin and end dates of first record are JAN/01/2009 AND FEB/01/2009.
begin and end dates of second record are JAN/15/2009 and FEB/15/2009.
Here dates are overlapping. with my select query i should get these two records.

I am using the following query. But it is running too slow. Could you please suggest a better sql ?
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select MGATH_clnt,MGATH_beg_DT,MGATH_END_DT,MGATH_NHIC_ERR_CD,id from MG_AUTH_AGNT m where m.MGATH_SERV_GRP = '1' and m.MGATH_STAT='A' and MGATH_clnt =(select distinct MGATH_clnt from MG_AUTH_AGNT d where d.MGATH_SERV_GRP = m.MGATH_SERV_GRP and d.MGATH_TYP = m.MGATH_TYP and m.MGATH_CLNT = d.MGATH_CLNT and d.MGATH_STAT=m.MGATH_STAT and m.idd.id and ((trunc(m.MGATH_beg_DT) >=trunc(d.MGATH_beg_DT) and trunc(m.MGATH_beg_DT) <=trunc(d.MGATH_END_DT) ) or (trunc(m.MGATH_END_DT) >= trunc(d.MGATH_beg_DT) and trunc(m.MGATH_END_DT) <= trunc(d.MGATH_END_DT)) or (trunc(m.MGATH_beg_DT) <= trunc(d.MGATH_beg_DT) and trunc(m.MGATH_END_DT) >= trunc(d.MGATH_END_DT)))) order by MGATH_clnt,MGATH_beg_DT

Edited by: user10727414 on Mar 17, 2009 1:36 AM
This post has been answered by BluShadow on Mar 17 2009
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 14 2009
Added on Mar 17 2009
15 comments
1,518 views