CODEWITHSUNDEEP
sql-server
Jerold Joel
Jerold Joel

Reputation: 251

Overlapping Dates in SQL

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.

Upvotes: 2

Views: 767

Answers (1)

SqlZim
SqlZim

Reputation: 38023

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

Upvotes: 2

Related Questions