1  
   2  -- PK is a placeholder for the Primary Key column(s)
   3  -- SoftPK is a placeholder for columns that need to match for overlap, but are not the PK
   4  -- Sometimes SoftPK is the PK, in which case you remove the <> PK line and match the SoftPK.
   5  -- The idea is to check for overlap on all rows except for yourself, since that always overlaps.
   6  --   This snippet assumes the EndDate column has a NULL to represent infinity.
   7  --   There are alternate syntaxes for this (compare or EndDate is null) but I've found
   8  --   the Coalesce to provide the best query plan usually. The constant itself is debatable and
   9  --   implementation dependent.
  10  select outer.*
  11  from DateEffectiveTable as outer
  12  where exists (
  13    select 1
  14    from DateEffectiveTable as inner
  15    where inner.PK <> outer.PK
  16      -- and inner.SoftPK = outer.SoftPK
  17      and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')
  18      and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate
  19  )
  20  
  21  
  22  -- Example Usage
  23  -- Assume we have a table containing the address history for a person
  24  -- A person may have multiple addresses at once (own multiple houses).
  25  -- A person may move away then back (same address, different time ranges).
  26  -- ie, multiple simultaneous unique records are possible, but not
  27  -- multiple simultaneous non-unique records (no self-overlap).
  28  -- Yes, it is usually an odd design to have an AddressID, but go with it.
  29  select outer.*
  30  from AddressHistory as outer
  31  where exists (
  32    select 1
  33    from AddressHistory as inner
  34    where outer.AddressID <> inner.AddressID
  35      and outer.PersonID = inner.PersonID
  36      and outer.Street = inner.Street
  37      and outer.Zip = inner.Zip
  38      and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')
  39      and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate
  40  
  41  
  42  -- Same example, but assume PK is a more "appropriate" one of
  43  -- { PersonID, Street, Zip, StartDate }
  44  select outer.*
  45  from AddressHistory as outer
  46  where exists (
  47    select 1
  48    from AddressHistory as inner
  49    where 
  50      not (
  51       outer.PersonID = inner.PersonID
  52       and outer.Street = inner.Street
  53       and outer.Zip = inner.Zip
  54       and outer.StartDate = inner.StartDate
  55      )
  56      and outer.PersonID = inner.PersonID
  57      and outer.Street = inner.Street
  58      and outer.Zip = inner.Zip
  59      and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')
  60      and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate