Find overlapping dates for specific code

[Almie]

I trying to write a query that will tell me for a specific code, if the effective date and end dates overlap. Here is an example of a record with 3 codes with effective and end dates.

Code Effect_date End_date
AHS 1/1/1900 7/6/2007
AHS 7/7/2007 12/31/2099
AHS 1/1/2009 12/31/2099 (overlay)

 

[imex]

Try:

select 
    t.*
from MyTable as t
join MyTable as p
    on p.Code = t.Code and
       p.Effect_date < t.Effect_date and
       p.End_date >= t.Effect_date

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[bborissov]

--- Preparing test table, you don't have to do this
DECLARE @Test TABLE (Code varchar(20),  Effect_date datetime, End_date datetime, ID int IDENTITY(1,1))
INSERT INTO @Test VALUES('AHS','19000101','20070706')
INSERT INTO @Test VALUES('AHS','20070707','20991231')
INSERT INTO @Test VALUES('AHS','20090101','20991231')
--- END


SELECT Tbl1.*
FROM @Test Tbl1
INNER JOIN @Test Tbl2 ON Tbl1.Code = Tbl2.Code AND
                            (Tbl1.Effect_date <=  Tbl2.End_date AND Tbl1.End_date >=  Tbl2.Effect_date) AND
                            Tbl1.ID <> Tbl2.ID -- This is Primary key for the table, just to filtering the same records

Borislav Borissov
VFP9 SP2, SQL Server

 

[Almie]

Awesome! It worked! Thank you Thank you Thank you!