MySQL Overlapping Date in Group

Question

Sorry if this question is basic, I am learning MySQL.

Let's assume I have one table with names and given dates:

+----+-----------+-----------+
|Name|StartDate  |EndDate    |
+----+-----------+-----------+
|A   |2013-08-29 |2014-12-29 |
|A   |2013-08-29 |2013-09-31 |
|B   |2015-01-02 |2015-03-20 |
|B   |2013-08-20 |2013-08-30 |
+----+-----------+-----------+

I would like to find the elements (Name) which has conflicting (overlapping) dates. Here, A has conflicting dates, B does not have, and code should return A. The overlapping between A and B does not matter. Thank you.

you've already asked this today: http://stackoverflow.com/questions/29737559/find-overlapping-dates-give-infinite-loop-mysql — pala_, Apr 20 '15 at 02:18
@pala_ . . . It is a different question. That question had no `name` separating the dates into different groups. — Gordon Linoff, Apr 20 '15 at 02:19
@GordonLinoff Can you tell me how this is different. To me it looks like he wants to find the names with overlapping dates, and in the other question he wants to find the IDs. — Mathemats, Apr 20 '15 at 02:32
@Mathemats . . . I think the `id`s are irrelevant in the other question. — Gordon Linoff, Apr 20 '15 at 02:33
In other question, I was reporting the `id`'s. Here I want to group by `Name`. — gurluk, Apr 20 '15 at 02:34

score 0 · Answer 1 · answered Apr 20 '15 at 02:22

You can do this with

select distinct name
from onetable t
where exists (select 1
              from onetable t2
              where t2.name = t.name and
                    t2.startdate < t.enddate and
                    t2.enddate > t.startdate
             );

The answer is similar to the previous answer, with the addition of the name comparison in the where clause.

The index that you want for this query is on table(name, startdate, enddate).

MySQL Overlapping Date in Group

1 Answers1