Overlapping Date Range Causing Cross Record Duplicates

Transact-SQL
1

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

2

Do something like this:

After you have populated #Data table

ALTER TABLE #DATA ADD N INT NOT NULL IDENTITY(1,1)

Then in your final query add one more condition (last AND clause in the code fragment below)

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
           AND t2.N > t1.N
1 Like
3

Hi James,
I am getting an error
Could not create IDENTITY attribute on nullable column 'N', table '#DATA'.

4

James, I used NOT NULL and that corrected it and the data looks great! THANK YOU SO MUCH! One question, how can I make the dates in the first column be the most recent? Right now it is sorting the first auth dates and then the 2nd auth dates. I have tried sorting by t2.N but that is not working.

5

Change the WHERE clause from

AND t2.N > t1.N

to

AND t2.N < t1.N