Post reply

FINDING RECORDS IN ONE TBALE NOT IN SYNC WITH ANOTHER TABLE

  • November 20, 2015 at 8:53 am

    #309820

    Here is the data for the question I am facing:

    CREATE TABLE #NEWSOURCE(CUSTOMERID VARCHAR(5),

    CATEGORYCODE VARCHAR(5),

    STARTDATE DATE,

    ENDDATE DATE)

    INSERT INTO #NEWSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE, ENDDATE) VALUES('12345', 'LAFUL', '01/01/2015', '10/31/2015')

    INSERT INTO #NEWSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE, ENDDATE) VALUES('12345', 'LA', '11/01/2015', NULL)

    INSERT INTO #NEWSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE, ENDDATE) VALUES('12355', 'SQ', '01/01/2015', NULL)

    INSERT INTO #NEWSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE, ENDDATE) VALUES('12375', 'LA', '01/01/2015', '09/30/2015')

    CREATE TABLE #OLDSOURCE (CUSTOMERID VARCHAR(5),

    CATEGORYCODE VARCHAR(5),

    STARTDATE DATE)

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12345', 'LAFUL', '01/01/2015')

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12345', 'LAFUL', '03/01/2015')

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12345', 'LAFUL', '10/01/2015')

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12345', 'LA', '11/01/2015')

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12355', 'SQ', '01/01/2015')

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12355', 'SQ', '05/01/2015')

    INSERT INTO OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12355', 'SQ', '11/01/2015')

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12375', 'LA', '01/01/2015')

    INSERT INTO #OLDSOURCE(CUSTOMERID, CATEGORYCODE, STARTDATE) VALUES('12375', 'LA', '10/01/2015')

    Which records in the #OLdSOURCE table are outside the date/category boundaries in the #NEWSOURCE table. Both categorycode and dates have to be considered.

    Any SQL suggestions?

  • November 20, 2015 at 9:28 am

    #1841396

    What would the expected results for this example be?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 20, 2015 at 9:39 am

    #1841397

    VALUES '12375', 'LA', '10/01/2015' in the #OLDSOURCE table would be an exception since the startdate

    is outside the date range for the CustomerID, CategoryCode and startdate/Enddate in the #NEWSOURCE table

  • November 20, 2015 at 10:10 am

    #1841405

    select OS.*

    from #OLDSOURCE OS

    inner join #NEWSOURCE NS on OS.CUSTOMERID = NS.CUSTOMERID and OS.CATEGORYCODE = NS.CATEGORYCODE

    and OS. Startdate not between NS.startdate and isnull(endDate,'9999-12-31')

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 20, 2015 at 10:13 am

    #1841407

    Thank you. I was just about to do the same thing.

Viewing 5 posts - 1 through 4 (of 4 total)

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