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