Overlapping Date Range Query Duplicating row in reverse

Question

Overlapping Date Range Query Duplicating row in reverse

shel 29143

Mr or Mrs. 500

Points: 502
More actions
March 5, 2020 at 3:23 pm

#3731458

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_ID      VARCHAR(15),
MEMBER_NAME    VARCHAR(100),
ASSIGNED_TO    VARCHAR(100),
CERT_AUTH_NUMBER       VARCHAR(15),
SERVICE_TYPE   VARCHAR(50),
SERVICE_CODE   VARCHAR(10),
SERVICE_START_DATE     DATE,
SERVICE_END_DATE       DATE,
DECISION       VARCHAR(50),
FISCAL_YEAR    VARCHAR(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)

You must be logged in to reply to this topic. Login to reply

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

SELECT *

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 -- I think this is redundant

--AND t2.SERVICE_CODE IS NOT NULL -- don't need this, it's eliminated by [t1.SERVICE_CODE = t2.SERVICE_CODE]

AND t1.CERT_AUTH_NUMBER > t2.CERT_AUTH_NUMBER -- this is the dupe-eliminator

/*
Say CERT_AUTH_NUMBER 10 and 14 overlap. 
The original query will pick up 10 and 14 from t1, and match to 14 and 10 from t2.
The small modification will restrict the output to t1 CERT_AUTH_NUMBER 14 matching t2 CERT_AUTH_NUMBER 10.
*/

^{“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

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 2

Chris is right about the code comments there about redundancy, although I wouldn't do the comparison on Auth_number like that, because lots of times they are not numeric, they might come from different systems, etc. that could cause you headaches

I would just add a ROW_NUMBER() column or an identity column to your temp table (so you know it's going to be numeric and comparable that way) and make it t1.rowNum < t2.rowNum.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses