Search code examples
sqlsql-serversql-server-2014

SQL dates Overlapping

I have a following table with data in sql. I wanted to know the dates which are overlapping.

ARN NO      From Date   To Date
1   106739  1/1/2015    3/31/2015
2   106739  1/1/2015    2/28/2015
3   106739  3/1/2015    3/31/2015
4   106739  5/1/2015    6/30/2015
5   106739  7/1/2015    9/30/2015
6   106739  9/1/2015    9/30/2015
7   106739  10/1/2015   12/31/2015
8   106739  10/1/2015   12/31/2015

In the above table, the first three records are overlapping. How do I write a query to find out? I need the below output

   NO  ARN NO   From Date   To Date
   4    106739  5/1/2015    6/30/2015

Thanks

Solution

  • You can use exists:

    select t.*
from t
where exists (select 1
              from t t2
              where t2.arnno = t.arrno and
                    t2.fromdate < t.todate and
                    t2.todate > t.fromdate
             );

    Two time periods overlap if the first starts before the second ends and ends after the second starts.