CODEWITHSUNDEEP
sql-serverdateduplicatesoverlapping
user3022918
user3022918

Reputation: 1

Querying for records with overlapping time periods in SQL - single table

My question is very similar to the one in this thread. However, I just have 1 table with fields ID Eff_Date End_Date. It actually is a bigger table with more fields, but I just listed those that are relevant here.

What is a simple and efficient way to find rows with time-interval overlaps in SQL?

I need to write a SQL statement to fetch records that have the same ID with overlapping effective date periods. A valid record usually has end_date as '99991231'.

select ID, DEFF, DEND
from table1
where ID  in (
      select ID
      from table1
      where DEND = 99991231
      group by ID
      having COUNT(*) >1) 
and DEND = 99991231
order by 1,2

Any thoughts will help!

Upvotes: 0

Views: 5291

Answers (3)

Adam Steen
Adam Steen

Reputation: 1

To find overlapping periods you just need to check that each effective begin date is <= to the other effective end dates.

So if you had 3 unique rows that you wanted to see if they overlap the logic would be (AND statements for each. Logic to determine unique records would be outside of what is listed below)

P1.BEG <= P2.END P1.BEG <= P3.END

P2.BEG <= P1.END P2.BEG <= P3.END

P3.BEG <= P1.END P3.BEG <= P2.END

If you take the same scenario but where not all 3 rows have to exist but if they do you want to pick up the overlapping periods, then you can use left joins for each of the table criteria and use the COALESCE function around each part such as

P1.BEG <= COALESCE(P2.END, '2999-12-31') This means use P2.END if it exists otherwise use 2999-12-31 which should always make the statement true. Therefore you pick up all history (based on your other criteria) but you will tie different rows together based upon overlapping periods.

Upvotes: 0

Anon
Anon

Reputation: 10908

SELECT
  t1.ID, t1.DEFF, t1.DEND, t2.DEFF, t2.DEND
FROM table1 t1
INNER JOIN table1 t2 ON (t2.ID = t1.ID AND t2.MyUniqueRowId > t1.MyUniqueRowId)
WHERE t1.DEND >= t2.DEFF
  AND t2.DEND >= t1.DEFF

Upvotes: 3

David Fleeman
David Fleeman

Reputation: 2638

Without testing, I believe the following will give you correct answer with no duplicates (trick to removing duplicates is to make sure t1.DEFF <= t2.DEFF always):

SELECT t1.ID,
        t1.DEFF AS DEFF1, t1.DEND AS DEND1
        t2.DEFF AS DEFF2, t2.DEND AS DEND2
FROM table1 t1
    -- exclude yourself in join (assuming that no two entries are identical)
    INNER JOIN table1 t2 ON t1.ID = t2.ID
        AND t1.DEFF <= t2.DEFF
            AND t1.DEFF != t2.DEFF
            AND t1.DEND != t2.DEND
WHERE
       -- check for overlap including t1 fully inside of t2
       (t1.DEFF BETWEEN t2.DEFF AND t2.DEND
           OR t1.DEND BETWEEN t2.DEFF AND t2.DEND)
   OR
       -- needed to additionally catch t2 fully inside of t1
       (t2.DEFF BETWEEN t1.DEFF AND t1.DEND
           OR t2.DEND BETWEEN t1.DEFF AND t1.DEND)

UPDATE: Realized that my JOIN limitation where t1.DEFF <= t2.DEFF means that t1 can never be inside of t2. Where clause can then be simplified to single check (eg, make sure that t2 does not start before t1 ends):

SELECT t1.ID,
        t1.DEFF AS DEFF1, t1.DEND AS DEND1
        t2.DEFF AS DEFF2, t2.DEND AS DEND2
FROM table1 t1
    -- exclude yourself in join (assuming that no two entries are identical)
    INNER JOIN table1 t2 ON t1.ID = t2.ID
        AND t1.DEFF <= t2.DEFF
            AND t1.DEFF != t2.DEFF
            AND t1.DEND != t2.DEND
WHERE
    t2.DEFF <= t1.DEND

Upvotes: 0

Related Questions