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
March 5, 2020 at 3:55 pm
Duplicate of https://www.sqlservercentral.com/forums/topic/overlapping-date-range-query-duplicating-row-in-reverse
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
March 5, 2020 at 8:58 pm
Closing. Please answer in the other post.
Viewing 3 posts - 1 through 2 (of 2 total)
The topic ‘Overlapping Date Ranges causing cross-duplicates’ is closed to new replies.