Find overlapping dates and exclude them in SQL server?

Question

Here is part of my table 'map': For example, Compare the A and D rows. The A date range is fully within the D date range, so we want to eliminate that row. (I do not want to delete this row, but my query result should not show that row).

sed id      code    startdate   enddate
101 1019    A       2002-12-02  2009-11-17 
101 1019    B       1986-01-02  2009-11-04 
101 1019    C       2009-07-01  2009-11-17 
101 1019    C       2002-12-02  2009-06-30 
101 1019    D       1986-01-03  2009-11-17 
101 1019    E       2007-10-15  2009-11-17 
101 1019    E       1992-01-31  1999-08-30 
101 1019    F       2007-11-26  2009-11-05 
101 1019    F       2007-09-05  2007-11-22 
101 1019    F       2007-07-06  2007-09-03 

Answer 1

This would seem to answer your question:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.startdate < t.startdate and t2.enddate >= t.enddate and
                        t2.sed = t.sed and t2.id = t.id and t2.code <> t.code
                 );