Pull Date-Effective Records that Overlap Each Other

jokeyxero (xero) — Wed, 04 Jun 2008 19:54:15 GMT

Basic framework and examples of how to pull records that overlap each other from a temporal (aka date-effective) database.



-- 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

Pull Date-Effective Records that Intersect A Given Range

jokeyxero (xero) — Fri, 16 May 2008 14:50:51 GMT

Basics of how to pull records that intersect a date range from a temporal (aka date-effective) database.



-- @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

DZone Snippets: data code

Pull Date-Effective Records that Overlap Each Other

Pull Date-Effective Records that Intersect A Given Range