Overlapping Date Ranges
-
Hi there,
I have looked in many forums and have been unable to find what I am looking for to get the results I need.Basically I am looking to see if a physician has 2 or more overlapping enrollment assignments (pcp_effdate and pcp_termdate). Only the 2 enrollids 112 and 114 should be flagged as overlapping.
CREATE TABLE test (
enrollid varchar(50),
pcptype varchar (3),
pcp_effdate date,
pcp_termdate date,
enr_effdate date,
enr_termdate date,
segtype varchar (3))INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000111','PCP ','2016-06-01','2016-12-31','2016-06-01','2017-02-28','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000111','PCP ','2016-12-01','2017-02-28','2016-06-01','2017-02-28','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000112','PCP ','2016-06-01','2016-06-16','2016-06-01','2016-08-31','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000112','PCP ','2016-06-15','2016-08-31','2016-06-01','2016-08-31','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000113','PCP ','2016-06-01','2016-06-07','2016-06-01','2017-12-31','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000113','PCP ','2016-06-08','2017-12-31','2016-06-01','2017-12-31','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000114','PCP ','2016-06-01','2016-12-31','2016-06-01','2017-12-31','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000114','PCP ','2017-10-18','2017-12-31','2016-06-01','2017-12-31','INT');
INSERT INTO test (enrollid,pcptype,pcp_effdate,pcp_termdate,enr_effdate,enr_termdate,Segtype) VALUES ('E000000114','PCP ','2017-10-01','2017-10-31','2016-06-01','2017-12-31','INT');Thanks for your help!
-
Ideally you would have a primary key on your table, an identity column or somthing similar so you won't find the same row overlapping with itself:
select t1.enrollid,
t1.pcp_effdate effdate1,
t1.pcp_termdate termdate1,
t2.pcp_effdate effdate2,
t2.pcp_termdate termdate2
from dbo.test t1
inner join dbo.test t2
on t2.enrollid=t1.enrollid
and t2.pcp_effdate < t1.pcp_termdate
and t2.pcp_termdate > t1.pcp_effdate
and t2.pcp_effdate > t1.pcp_effdate
111 also has overlapping dates. -
That is EXACTLY what I was looking for! Thank you so much and for the quick reply!
- shel 29143 - Tuesday, March 5, 2019 8:17 AM
Just to aid in teaching someone to fish... please see the following article for why Jonathan's code does what you want.
http://www.sqlservercentral.com/articles/T-SQL/105968/--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
shel, you probably also want to add a check of effdate<=termdate, because sometimes ops "voids" affiliations that they can't delete by putting the termdate<effdate.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply