0

I am trying to compare two tables. The way our program works is that if a person was changed, it updates the record and also throws the original data into a new table.

I was able to create a query that works. However, it just returns the row(s) that have changed. Somehow I need it to return both the old and the new, and point out what changed. Is there a way to do that?

So in this example, throwing another thing in there (sorry) Jon Doe should not show up, because nothing really changed. One of his phone numbers got moved from one phone field to another. There are so many like this and my new version still isn't handling them properly.

Jane should show up because her email changed. However it's just returning that record. I can't see what changed, or what the original value is.

Either way it has to say something like this. (I'll skip the details) but in some format so they can see what changed. 

BEFORE                    AFTER             CHANGED
Name: Jane Smith          Jane Smith
Address: 111 East Grove   111 East Grove
Etc.
Email:  janeR@gmail.com   janes@gmail.com    Yes

ETA Changes from Conrad (Thanks)

   CREATE TABLE #Source 
    (
    ID varchar(50), nameFull varchar(50), salutation varchar(50), nameFirst varchar(50), nameLast varchar(50), addressLine1 varchar(50), 
    addressLine2 varchar(50), addressCity varchar(50), addressState varchar(50), addressZip varchar(50), 
    phone1full varchar(50), phone2full varchar(50), phone3full varchar(50), phone4full varchar(50), phone5full varchar(50), email1 varchar(50)
    )
    CREATE TABLE #Target
    (pov_Prospect varchar(50), 
    pov_nameFull varchar(50), pov_salutation varchar(50), pov_nameFirst varchar(50), pov_nameLast varchar(50), 
    pov_addressLine1 varchar(50), pov_addressLine2 varchar(50), pov_addressCity varchar(50), pov_addressState varchar(50), pov_addressZip varchar(50), 
    pov_phone1full varchar(50), pov_phone2full varchar(50), pov_phone3full varchar(50), pov_phone4full varchar(50), pov_phone5full varchar(50), pov_email1 varchar(50)
    )

    -- Jon
    INSERT INTO #Source (ID, nameFull, salutation, nameFirst, nameLast, addressLine1, addressLine2, addressCity, addressState, addressZip, 
    phone1full, phone2full, phone3full, phone4full, phone5full, email1)
    VALUES ('1', 'Jon Doe', 'Jon', 'Jon', 'Doe', '1234 West Elm', NULL, 'Chicago', 'IL', '45880', 
    '1112223333', NULL, '4445556666', NULL, NULL, 'jond@gmail.com');
    INSERT INTO #Target
    (pov_Prospect, pov_nameFull, pov_salutation, pov_nameFirst, pov_nameLast, 
    pov_addressLine1, pov_addressLine2, pov_addressCity, pov_addressState, pov_addressZip, 
    pov_phone1full, pov_phone2full, pov_phone3full, pov_phone4full, pov_phone5full, pov_email1)
    VALUES ('1', 'Jon Doe', 'Jon', 'Jon', 'Doe', '1234 West Elm', NULL, 'Chicago', 'IL', '45880', 
    '1112223333', '4445556666', NULL, NULL, NULL, 'jond@gmail.com');
    --Jane
    INSERT INTO #Source (ID, nameFull, salutation, nameFirst, nameLast, addressLine1, addressLine2, addressCity, addressState, addressZip, 
    phone1full, phone2full, phone3full, phone4full, phone5full, email1)
    VALUES ('2', 'Jane Smith', 'Jane', 'Jane', 'Smith', '111 East Grove', 'Suite 101', 'Denver', 'CO', '75365', 
    '8882223333', NULL, NULL, NULL, NULL, 'janes@gmail.com');
    INSERT INTO #Target (pov_Prospect, pov_nameFull, pov_salutation, pov_nameFirst, pov_nameLast, 
    pov_addressLine1, pov_addressLine2, pov_addressCity, pov_addressState, pov_addressZip, 
    pov_phone1full, pov_phone2full, pov_phone3full, pov_phone4full, pov_phone5full, pov_email1)
    VALUES ('2', 'Jane Smith', 'Jane', 'Jane', 'Smith', '111 East Grove', 'Suite 101', 'Denver', 'CO', '75365', 
    '8882223333', NULL, NULL, NULL, NULL, 'janeR@gmail.com');
    --Bob - added after the fact...I didn't know there were so many like Bob
    INSERT INTO #Source (ID, nameFull, salutation, nameFirst, nameLast, addressLine1, addressLine2, addressCity, addressState, addressZip, 
    phone1full, phone2full, phone3full, phone4full, phone5full, email1)
    VALUES ('3', 'Bob Doe', 'Bob', 'Bob', 'Doe', '1234 West Elm', NULL, 'Chicago', 'IL', '45880', 
    '111', NULL, '111', NULL, NULL, 'Bobd@gmail.com');
    INSERT INTO #Target
    (pov_Prospect, pov_nameFull, pov_salutation, pov_nameFirst, pov_nameLast, 
    pov_addressLine1, pov_addressLine2, pov_addressCity, pov_addressState, pov_addressZip, 
    pov_phone1full, pov_phone2full, pov_phone3full, pov_phone4full, pov_phone5full, pov_email1)
    VALUES ('3', 'Bob Doe', 'Bob', 'Bob', 'Doe', '1234 West Elm', NULL, 'Chicago', 'IL', '45880', 
    '111', NULL, NULL, NULL, NULL, 'Bobd@gmail.com');

------------------------------------------------------------------------------------------------------------------------------------------------------------------

      ;with data as (
    SELECT
            s.id, 
            ISNULL(s.nameFull, '') nameFull , 
            ISNULL(s.salutation, '') salutation , 
            ISNULL(s.nameFirst, '') nameFirst , 
            ISNULL(s.nameLast, '') nameLast , 
            ISNULL(s.addressLine1, '') addressLine1 , 
            ISNULL(s.addressLine2, '') addressLine2 , 
            ISNULL(s.addressCity, '') addressCity , 
            ISNULL(s.addressState, '') addressState , 
            ISNULL(s.addressZip, '') addressZip , 
            ISNULL(s.phone1full, '') phone1full , 
            ISNULL(s.phone2full, '') phone2full , 
            ISNULL(s.phone3full, '') phone3full , 
            ISNULL(s.phone4full, '') phone4full , 
            ISNULL(s.phone5full, '') phone5full , 
            ISNULL(s.email1, '') email1 ,
            ISNULL(t.pov_nameFull, '') pov_nameFull , 
            ISNULL(t.pov_salutation, '') pov_salutation , 
            ISNULL(t.pov_nameFirst, '') pov_nameFirst , 
            ISNULL(t.pov_nameLast, '') pov_nameLast , 
            ISNULL(t.pov_addressLine1, '') pov_addressLine1 , 
            ISNULL(t.pov_addressLine2, '') pov_addressLine2 , 
            ISNULL(t.pov_addressCity, '') pov_addressCity , 
            ISNULL(t.pov_addressState, '') pov_addressState , 
            ISNULL(t.pov_addressZip, '') pov_addressZip , 
            ISNULL(t.pov_phone1full, '') pov_phone1full , 
            ISNULL(t.pov_phone2full, '') pov_phone2full , 
            ISNULL(t.pov_phone3full, '') pov_phone3full , 
            ISNULL(t.pov_phone4full, '') pov_phone4full , 
            ISNULL(t.pov_phone5full, '') pov_phone5full , 
            ISNULL(t.pov_email1, '') pov_email1 


    FROM [#Source] [S]
        INNER JOIN [#Target] [T]
        ON [S].[ID] = [T].[pov_Prospect]
    WHERE
              ISNULL( s.nameFull, '') <> ISNULL( t.pov_nameFull, '') OR 
          ISNULL( s.salutation, '') <> ISNULL( t.pov_salutation, '') OR 
          ISNULL( s.nameFirst, '') <> ISNULL( t.pov_nameFirst, '') OR 
          ISNULL( s.nameLast, '') <> ISNULL( t.pov_nameLast, '') OR 
          ISNULL( s.addressLine1, '') <> ISNULL( t.pov_addressLine1, '') OR 
          ISNULL( s.addressLine2, '') <> ISNULL( t.pov_addressLine2, '') OR 
          ISNULL( s.addressCity, '') <> ISNULL( t.pov_addressCity, '') OR 
          ISNULL( s.addressState, '') <> ISNULL( t.pov_addressState, '') OR 
          ISNULL( s.addressZip, '') <> ISNULL( t.pov_addressZip, '') OR 
          ISNULL( s.phone1full, '') <> ISNULL( t.pov_phone1full, '') OR 
          ISNULL( s.phone2full, '') <> ISNULL( t.pov_phone2full, '') OR 
          ISNULL( s.phone3full, '') <> ISNULL( t.pov_phone3full, '') OR 
          ISNULL( s.phone4full, '') <> ISNULL( t.pov_phone4full, '') OR 
          ISNULL( s.phone5full, '') <> ISNULL( t.pov_phone5full, '') OR 
          ISNULL( s.email1, '') <> ISNULL( t.pov_email1, '')  

            OR (s.phone1full <> t.pov_phone1full AND s.phone1full <> t.pov_phone2full AND s.phone1full <> t.pov_phone3full AND s.phone1full <> t.pov_phone4full AND s.phone1full <> t.pov_phone5full ) 
            OR (s.phone2full <> t.pov_phone1full AND s.phone2full <> t.pov_phone2full AND s.phone2full <> t.pov_phone3full AND s.phone2full <> t.pov_phone4full AND s.phone2full <> t.pov_phone5full ) 
            OR (s.phone3full <> t.pov_phone1full AND s.phone3full <> t.pov_phone2full AND s.phone3full <> t.pov_phone3full AND s.phone3full <> t.pov_phone4full AND s.phone3full <> t.pov_phone5full ) 
            OR (s.phone4full <> t.pov_phone1full AND s.phone4full <> t.pov_phone2full AND s.phone4full <> t.pov_phone3full AND s.phone4full <> t.pov_phone4full AND s.phone4full <> t.pov_phone5full ) 
            OR (s.phone5full <> t.pov_phone1full AND s.phone5full <> t.pov_phone2full AND s.phone5full <> t.pov_phone3full AND s.phone5full <> t.pov_phone4full AND s.phone5full <> t.pov_phone5full )
    )

    , fieldsToRows 
         AS (SELECT 
                id,
                field,
                Value

             FROM   data p UNPIVOT
                           (value FOR field IN (nameFull, pov_nameFull, 
                                                    salutation, pov_salutation, 
                                                    nameFirst, pov_nameFirst, 
                                                    nameLast, pov_nameLast, 
                                                    addressLine1, pov_addressLine1, 
                                                    addressLine2, pov_addressLine2, 
                                                    addressCity, pov_addressCity, 
                                                    addressState, pov_addressState, 
                                                    addressZip, pov_addressZip, 
                                                    phone1full, pov_phone1full, 
                                                    phone2full, pov_phone2full, 
                                                    phone3full, pov_phone3full, 
                                                    phone4full, pov_phone4full, 
                                                    phone5full, pov_phone5full, 
                                                    email1, pov_email1))
                    AS unpvt
            ) 
            SELECT 
                    curr.id,
                    curr.field,
                    curr.value new_value,
                    prev.value  old_value,
                    CASE WHEN curr.value <> prev.value THEN 'Yes'  END Changed


    FROM 
            fieldsToRows curr 
            INNER  JOIN  fieldsToRows prev
            ON curr.ID = prev.id
                AND curr.field  = 'pov_' + prev.field
nikih
  • 1
  • 3
  • Are looking for something like this https://stackoverflow.com/questions/7741527/how-can-i-write-a-query-to-extract-individual-changes-from-snapshots-of-data?noredirect=1&lq=1 – Conrad Frix Mar 21 '18 at 19:12
  • Make it easy to help you - https://stackoverflow.com/help/mcve – jarlh Mar 21 '18 at 20:04
  • If I am missing something please clarify. I thought I included everything I am supposed to. I'm new here. Thanks. – nikih Mar 21 '18 at 20:06
  • After looking at that example it doesn't look like it's comparing two tables, so I don't think it's going to work. If anyone could offer suggestions I would really appreciate it. I've been stuck for days and really frustrated. Thank you! – nikih Mar 21 '18 at 20:22

2 Answers2

0

The main difference between your problem and How can I write a query to extract individual changes from snapshots of data? is that you only have two records to contend with rather then N records. Additionally you seem to want all the values but want to know if they changed.

;with data as (
SELECT *
FROM [#Source] [S]
    INNER JOIN [#Target] [T]
    ON [S].[ID] = [T].[pov_Prospect]

)

, fieldsToRows 
     AS (SELECT 
            id,
            field,
            Value

         FROM   data p UNPIVOT (value FOR field IN (nameFull, pov_nameFull, 
                                                salutation, pov_salutation, 
                                                nameFirst, pov_nameFirst, 
                                                nameLast, pov_nameLast, 
                                                addressLine1, pov_addressLine1, 
                                                addressLine2, pov_addressLine2, 
                                                addressCity, pov_addressCity, 
                                                addressState, pov_addressState, 
                                                addressZip, pov_addressZip, 
                                                phone1full, pov_phone1full, 
                                                phone2full, pov_phone2full, 
                                                phone3full, pov_phone3full, 
                                                phone4full, pov_phone4full, 
                                                phone5full, pov_phone5full, 
                                                email1, pov_email1    ) ) 
                AS unpvt
        ) 
SELECT 
        curr.id,
        curr.field,
        curr.value new_value,
        prev.value  old_value,
        CASE WHEN curr.value <> prev.value THEN 'Yes'  END Changed

FROM 
        fieldsToRows curr 
        INNER  JOIN  fieldsToRows prev
        ON curr.ID = prev.id
            AND 'pov_' + curr.field  =  prev.field

Demo

Because your data has nulls you'll have to contend with Handle NULL value in UNPIVOT

In my demo I added ISNULL to each of the fields but left it out in the above for for the sake of brevity

If you want to eliminate the rows that don't have changes you'll need to add a series of OR clauses like ISNULL( s.nameFull, '') <> ISNULL( t.pov_nameFull, '')

See Demo

This doesn't deal with fields that moved. For that you'll need to do something OR (phone1 <> pov_phone1 and phone1 <> pov_phone2 AND... ) OR

I'll leave it to you on how to deal with different datatypes (yours were all varchar(50) )

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Thank you. I am very sorry I did not supply enough data. Your solution is close and I really appreciate your help! I do not want to return all the data. That would be a nightmare. What I'm grabbing and putting in the temp tables are ones that have been flagged as changed. However, it doesn't do a good job, so anything that was null and isn't, get's flagged. So this will work, but I think I need to handle nulls in here too. Is that possible? Otherwise they're going to be sorting through a mess. So for your example, only Jane should show up. And again, thank you! – nikih Mar 21 '18 at 21:10
  • Where would that giant null clause go? Thanks. Sorry I'm so lost. – nikih Mar 21 '18 at 21:19
  • You just need to change the case to `CASE WHEN Isnull(curr.value, '') <> Isnull(prev.value,'') THEN 'Yes' END Changed` and use the second demo – Conrad Frix Mar 21 '18 at 21:20
  • Thanks, that makes sense now. I really appreciate your help. How do I eliminate Jon then? – nikih Mar 21 '18 at 21:22
  • Thank you. The data is all varchar's. :0 The second example Jon is still being returned. Is there a way I can just return Jane and not Jon? Does that make sense? Since nothing of his changed, I don't want to see him at all. – nikih Mar 21 '18 at 21:29
  • @nikih I updated my answer. It doesn't eliminate Jon (not sure why I didn't check the output the first time) but it accurately deals with nulls – Conrad Frix Mar 21 '18 at 22:06
  • I added in the clause for looking up phone numbers. However, it looks like someone will take a phone number, and put it in a few other phone number fields too. Just for good measure. (why????) I'm stuck on how to figure that in. I updated my code above, since it won't fit in here. – nikih Mar 27 '18 at 18:44
0

I started from Conrad Frix's answer, but I separated the unpivots for the source and the target, so we can also find the differences involving nulls:

;with SourceFieldsToRows AS (
    SELECT id, field, Value
    FROM   #Source s UNPIVOT (Value FOR field IN (nameFull, salutation, nameFirst, nameLast, addressLine1, addressLine2, addressCity, addressState, addressZip, phone1full, phone2full, phone3full, phone4full, phone5full, email1)) u
), TargetFieldsToRows AS (
    SELECT u.pov_Prospect, field, Value
    FROM   #Target t UNPIVOT (Value FOR field IN (pov_nameFull, pov_salutation, pov_nameFirst, pov_nameLast, pov_addressLine1, pov_addressLine2, pov_addressCity, pov_addressState, pov_addressZip, pov_phone1full, pov_phone2full, pov_phone3full, pov_phone4full, pov_phone5full, pov_email1)) u
)
SELECT  ISNULL(curr.id,prev.pov_Prospect) AS id,
        ISNULL(curr.field,prev.field) AS field,
        curr.value AS new_value,
        prev.value AS old_value,
        CASE WHEN curr.value <> prev.value OR curr.value IS NULL AND prev.value IS NOT NULL OR curr.value IS NOT NULL AND prev.value IS NULL THEN 'Yes' END Changed
FROM SourceFieldsToRows curr 
FULL OUTER  JOIN  TargetFieldsToRows prev 
    ON curr.ID = prev.pov_Prospect AND 'pov_' + curr.field  =  prev.field
Razvan Socol
  • 5,426
  • 2
  • 20
  • 32
  • Thank you! It is still returning all three people. Am I missing something? – nikih Mar 27 '18 at 19:41
  • There are differences in each row: for Jon Doe, the phone3full is new and phone2full is missing; for Jane Smith, the email1 is different; for Bob Doe, phone3full is new. I understood that you need all the rows which had any type of change, isn't it right? – Razvan Socol Mar 27 '18 at 19:46
  • Thanks for responding. I appreciate it. I only need Jane. They don't care if the same phone number was moved around. Or if there were three of the same phone number and someone deleted two duplicates. Or if someone added duplicate phone numbers. Thank you. – nikih Mar 27 '18 at 19:50