Overlapping Dates in SQL

Question

I want a query to identify the Vehicle Registration Numbers having more than 1 policy with different Insurance Company ID, with overlapping dates. Check this image for the datas

 SELECT Vehicle_N0.*
   FROM excel as Vehicle_N0
  WHERE Vehicle_N0 IN (SELECT Vehicle_N0  
                         FROM excel 
                        GROUP BY Vehicle_N0    
                       HAVING COUNT(DISTINCT Insurance_id) > 1) 
  ORDER BY vehicle_n0

using this query i got the Vehicle Registration Numbers having more than 1 policy with different Insurance Company ID but how to get the overlapping dates.

I tried this query

SELECT * 
  FROM excel 
 WHERE begin_date <= end_date 
    OR begin_date >= end_date;

But i didn't get the overlapped dates.

please post expected result,actual result as text and not images — TheGameiswar, Sep 06 '17 at 06:37
The expected result is i want to get the overlapped dates from my table. I tried to do that but i am failed — Jerold Joel, Sep 06 '17 at 06:42
Look at this link to know more on how to improve the question:https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, Sep 06 '17 at 06:46
Have you checked this question? Maybe it will help you? https://stackoverflow.com/questions/4490553/detect-overlapping-date-ranges-from-the-same-table — Cosmin, Sep 06 '17 at 11:35

SqlZim · Accepted Answer · 2017-09-07T12:32:38.893

2

Using exists() to see if a Vehicle_N0 has an overlapping entry with a different Insurance_Id:

select v.*
from excel as v
where exists ( -- only return rows where this query would return row(s)
  select 1
  from excel as i 
  where i.Vehicle_N0 = v.Vehicle_N0      -- Vehicle_N0 is the same
    and i.Insurance_Id <> v.Insurance_Id -- Insurance_Id is not the same
    and i.end_date > v.begin_date        -- date range overlaps
    and v.end_date > i.begin_date        -- date range overlaps
  )
order by v.Vehicle_N0

edited Sep 07 '17 at 12:32

answered Sep 06 '17 at 12:16

SqlZim

36,108
6
38
56

I am getting the correct result but, i don't understand how it works.... – Jerold Joel Sep 07 '17 at 05:28
@JeroldJoel I have updated my answer with some comments, let me know if that helps. Is there a particular part you do not understand? – SqlZim Sep 07 '17 at 12:24
@JeroldJoel Happy to help! – SqlZim Sep 07 '17 at 12:41

Overlapping Dates in SQL

1 Answers1

Linked