0

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!

Community
  • 1
  • 1
user3022918
  • 1
  • 1
  • 1

3 Answers3

3
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
Anon
  • 10,660
  • 1
  • 29
  • 31
  • My understanding is that you would want to do that join on t2.ID = t1.ID as the ID is NOT unique according to the question: "fetch records that have the same ID with overlapping" – David Fleeman Nov 22 '13 at 18:59
  • The edited query will now have a lot of false positives... It will also have duplicates. And now there was a new edit :) – David Fleeman Nov 22 '13 at 19:06
  • Pretty sure that this is not necessary `AND t2.DEND >= t1.DEFF` – David Fleeman Nov 22 '13 at 19:15
  • Also, as for the false positive, you would always return a row that represents that you overlap with yourself for each ID. – David Fleeman Nov 22 '13 at 19:17
  • I am making that assumption -- without it, you will always get 1 row per unique ID with equal DEFF and DEND values even when there is really no dup at all. That is the definition of a false positive. – David Fleeman Nov 22 '13 at 19:22
0

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
David Fleeman
  • 2,588
  • 14
  • 17
  • You don't need to treat one range fully overlapping the other as special cases, because they aren't. If T2 starts before T1 ends AND T1 starts before T2 ends, they overlap, no matter how they are arranged. The extra checks are redundant – Anon Nov 22 '13 at 19:13
  • @Anon - good catch, my original query needed that, but my added JOIN condition made the extra checks unnecessary. I have updated the query. – David Fleeman Nov 22 '13 at 19:13
0

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.

Adam Steen
  • 1