Overlapping Date Ranges causing cross-duplicates

Question

Overlapping Date Ranges causing cross-duplicates

Mr or Mrs. 500

Points: 502

March 5, 2020 at 1:58 pm

I have a query where I am trying to find out if there are 2 authorizations for the same time frame. What is happening though that I can't figure out is I get the result row which is the auth details for the overlapping auths, but then I get a second row with the same data, only reversed.

For example,

memberId Auth# Start_Date End Date Auth# Start_date End_date

123 20214791 12/1/2019 4/30/2020 20108767 12/1/2019 5/31/2020

123 20108767 12/1/2019 5/31/2020 20214791 12/1/2019 4/30/2020

I can't figure out why it is crossing like that or how to fix it. Here is some sample data and my query. Thanks for looking at it!

DROP TABLE #AUTHS
CREATE TABLE #AUTHS (
MEMBER_IDVARCHAR(15),
MEMBER_NAMEVARCHAR(100),
ASSIGNED_TOVARCHAR(100),
CERT_AUTH_NUMBERVARCHAR(15),
SERVICE_TYPEVARCHAR(50),
SERVICE_CODEVARCHAR(10),
SERVICE_START_DATEDATE,
SERVICE_END_DATEDATE,
DECISIONVARCHAR(50),
FISCAL_YEARVARCHAR(10))


INSERT INTO #AUTHS (MEMBER_ID,MEMBER_NAME,ASSIGNED_TO,CERT_AUTH_NUMBER,SERVICE_TYPE,SERVICE_CODE,SERVICE_START_DATE,SERVICE_END_DATE,DECISION,FISCAL_YEAR) VALUES ('123','STONE, OLIVER','Gomez, Madeline','20116057','RESPITE CARE','T1005','2020-01-01','2020-06-30','Approved','2019-20');
INSERT INTO #AUTHS (MEMBER_ID,MEMBER_NAME,ASSIGNED_TO,CERT_AUTH_NUMBER,SERVICE_TYPE,SERVICE_CODE,SERVICE_START_DATE,SERVICE_END_DATE,DECISION,FISCAL_YEAR) VALUES ('123','STONE, OLIVER','LTSS Auths','710000828','RESPITE CARE','T1005','2020-01-01','2020-06-30','Approved','2019-20');
INSERT INTO #AUTHS (MEMBER_ID,MEMBER_NAME,ASSIGNED_TO,CERT_AUTH_NUMBER,SERVICE_TYPE,SERVICE_CODE,SERVICE_START_DATE,SERVICE_END_DATE,DECISION,FISCAL_YEAR) VALUES ('456','MITCHELL, STANLEY','Bright, Senita','20214791','PERSONAL CARE','S5126','2019-12-01','2020-04-30','Approved','2019-20');
INSERT INTO #AUTHS (MEMBER_ID,MEMBER_NAME,ASSIGNED_TO,CERT_AUTH_NUMBER,SERVICE_TYPE,SERVICE_CODE,SERVICE_START_DATE,SERVICE_END_DATE,DECISION,FISCAL_YEAR) VALUES ('456','MITCHELL, STANLEY','Gomez, Madeline','20108767','PERSONAL CARE','S5126','2019-12-01','2020-05-31','Approved','2019-20');

IF OBJECT_ID('tempdb.dbo.#DATA') IS NOT NULL
  DROP TABLE #DATA
SELECT DISTINCT
[MEMBER_ID]
,MEMBER_NAME
,ASSIGNED_TO
,[CERT_AUTH_NUMBER]
,SERVICE_TYPE
,SERVICE_CODE
,SERVICE_START_DATE
,SERVICE_END_DATE
,DECISION
,FISCAL_YEAR 
INTO #DATA
FROM 
#AUTHS

SELECT DISTINCT
t1.[MEMBER_ID]
,t1.MEMBER_NAME
,t1.ASSIGNED_TO
,t1.[CERT_AUTH_NUMBER]
,t1.SERVICE_TYPE
,t1.SERVICE_CODE
,t1.SERVICE_START_DATE
,t1.SERVICE_END_DATE
,t1.DECISION
,t1.FISCAL_YEAR
,t2.ASSIGNED_TO
,t2.[CERT_AUTH_NUMBER]
,t2.SERVICE_TYPE
,t2.SERVICE_CODE
,t2.SERVICE_START_DATE
,t2.SERVICE_END_DATE
,t2.DECISION
,t2.FISCAL_YEAR
FROM #DATA t1
INNER JOIN #DATA t2
ON t1.MEMBER_ID = t2.MEMBER_ID
AND t1.SERVICE_CODE = t2.SERVICE_CODE
AND t2.SERVICE_START_DATE <= t1.SERVICE_END_DATE
AND t2.SERVICE_END_DATE >= t1.SERVICE_START_DATE
AND t2.SERVICE_START_DATE >= t1.SERVICE_START_DATE
AND t2.SERVICE_CODE IS NOT NULL
and t1.CERT_AUTH_NUMBER <> t2.CERT_AUTH_NUMBER 

ORDER BY T1.MEMBER_ID, T1.SERVICE_START_DATE, T1.SERVICE_END_DATE, T2.SERVICE_START_DATE, T2.SERVICE_END_DATE

Viewing 3 posts - 1 through 2 (of 2 total)

The topic ‘Overlapping Date Ranges causing cross-duplicates’ is closed to new replies.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Duplicate of https://www.sqlservercentral.com/forums/topic/overlapping-date-range-query-duplicating-row-in-reverse

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 734449

March 5, 2020 at 8:58 pm

#3731567

Closing. Please answer in the other post.