Overlapping Date Ranges causing cross-duplicates

  • shel 29143

    SSC-Addicted

    Points: 499

    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
  • ChrisM@Work

    SSC Guru

    Points: 186087

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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717951

    Closing. Please answer in the other post.

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

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