-- PK is a placeholder for the Primary Key column(s)
-- SoftPK is a placeholder for columns that need to match for overlap, but are not the PK
-- Sometimes SoftPK is the PK, in which case you remove the <> PK line and match the SoftPK.
-- The idea is to check for overlap on all rows except for yourself, since that always overlaps.
--   This snippet assumes the EndDate column has a NULL to represent infinity.
--   There are alternate syntaxes for this (compare or EndDate is null) but I've found
--   the Coalesce to provide the best query plan usually. The constant itself is debatable and
--   implementation dependent.
select outer.*
from DateEffectiveTable as outer
where exists (
  select 1
  from DateEffectiveTable as inner
  where inner.PK <> outer.PK
    -- and inner.SoftPK = outer.SoftPK
    and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')
    and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate
)


-- Example Usage
-- Assume we have a table containing the address history for a person
-- A person may have multiple addresses at once (own multiple houses).
-- A person may move away then back (same address, different time ranges).
-- ie, multiple simultaneous unique records are possible, but not
-- multiple simultaneous non-unique records (no self-overlap).
-- Yes, it is usually an odd design to have an AddressID, but go with it.
select outer.*
from AddressHistory as outer
where exists (
  select 1
  from AddressHistory as inner
  where outer.AddressID <> inner.AddressID
    and outer.PersonID = inner.PersonID
    and outer.Street = inner.Street
    and outer.Zip = inner.Zip
    and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')
    and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate


-- Same example, but assume PK is a more "appropriate" one of
-- { PersonID, Street, Zip, StartDate }
select outer.*
from AddressHistory as outer
where exists (
  select 1
  from AddressHistory as inner
  where 
    not (
     outer.PersonID = inner.PersonID
     and outer.Street = inner.Street
     and outer.Zip = inner.Zip
     and outer.StartDate = inner.StartDate
    )
    and outer.PersonID = inner.PersonID
    and outer.Street = inner.Street
    and outer.Zip = inner.Zip
    and inner.StartDate <= coalesce(outer.EndDate, '9999-12-31')
    and coalesce(inner.EndDate, '9999-12-31') > outer.StartDate

-- @rangeStart is the start of the date range of interest
-- @rangeEnd is the end of the date range of interest
-- If you use null to signify infinity, then use the commented line instead of the one above it.
select *
from DateEffectiveTable
where StartDate < @rangeEnd
  and EndDate >= @rangeStart
  -- and coalesce(endDate, '9999-12-31') >= @rangeStart