Current position:  Home > Default > Need to find rows with overlapping dates

Need to find rows with overlapping dates

Time:November 30
Advertisement
I have a table that has FirstName, LastName, EnrollmentFrom and EnrollmentTo
I need to find all names where they have overlapping dates.  For example this is invalid:
Name       EnrollmentFrom     EnrollmentTo
John Doe   1/1/2014              1/1/2015
John Doe   2/5/2014              2/5/2014
This would be valid:
John Doe   1/1/2014              1/1/2015
John Doe   1/2/2015              1/1/2016
This is just an example as the dates could be anything in one of the other enrollments.
Any help is greatly appreciated.
Advertisement
Here is what I have based on the last post but it also returns ones that are valid:
SELECT ffev.PatientLastName, ffev.PatientFirstName, ffev.EnrollmentFrom, ffev.EnrollmentTo
FROM PAPPortal..FeedFileEnrollmentValues ffev
OUTER APPLY (select top 1 b.* from PAPPortal..FeedFileEnrollmentValues b where ffev.PatientID = b.PatientID and b.EnrollmentFrom < ffev.EnrollmentTo and b.EnrollmentTo > ffev.EnrollmentFrom and ffev.PatientID <> b.PatientID) overlap
WHERE ffev.EnrollmentTo IS NOT NULL
GROUP BY ffev.PatientLastName, ffev.PatientFirstName, ffev.EnrollmentFrom, ffev.EnrollmentTo
ORDER BY ffev.PatientLastName, ffev.PatientFirstName, ffev.EnrollmentFrom, ffev.EnrollmentTo
Name              EnrollmentFrom         EnrollmentTo
John Doe            2013-02-08            2014-02-07
John Doe            2014-08-15            2015-08-15
hi
it seems you have given (= and <>) which is always return false(no records)
 where ffev.PatientID = b.PatientID and b.EnrollmentFrom < ffev.EnrollmentTo and b.EnrollmentTo > ffev.EnrollmentFrom and
       ffev.PatientID <> b.PatientID
Note : i have given
where a.ID=b.ID and b.DateFrom<a.DateTo and b.DateTo>a.DateFrom and
a.rno<>b.rno
If you don't have any unique column to differentiate you  can create rno with CTE and use like below
DECLARE @Data AS TABLE
-- rno int identity(1,1),
ID int,
DateFrom datetime,
DateTo datetime
INSERT INTO @Data VALUES (1, '1/1/2014', '1/1/2015')
INSERT INTO @Data VALUES (1, '2/5/2014', '2/5/2014')
INSERT INTO @Data VALUES (2, '1/1/2014', '12/31/2014')
INSERT INTO @Data VALUES (2, '1/2/2015', '12/31/2016')
;WITH CTE1 as
SELECT ROW_NUMBER() over(PARTITION by ID order by DateFrom,DateTo ) rno,* FROM @Data
SELECT a.*, overlap.rno,overlap.DateFrom,overlap.DateTo
FROM CTE1 a
OUTER APPLY (select top 1 b.* from CTE1 b where a.ID=b.ID
and b.DateFrom<a.DateTo
and b.DateTo>a.DateFrom and a.rno<>b.rno) overlapwhere overlap.ID is not null
Thanks
Saravana Kumar C