I have a customer list table holding all data for a number of our branches. We have recently had a load of customers move from branch 02 to branch 04 but have all now got a different customer number. small example below:

table.customers

So I need to now check that all data has been moved from branch 02 to branch 04 correct, with only the cust_Code being allowed to be different on the columns stated. I do have a list of branch 02 customer codes and the new corresponding branch 04 customer codes. so can tell the query a customer code at branch 02 and to check the customer code at branch 04 to see if the rest of the columns match.
As you can see above the first customer 1234 everything is fine and matches so this can pass the check.
But the customers in bold and italics have something typed incorrect.

I am wanting to write some T-SQL query to now help me identify which customers have non matching data.

CodePudding user response：

Sorry, but you should not base your query in "hope".

First, make sure you make a table with the customer codes before and after the branch move. Let's assume a table named [pairs] with two columns, [branch02code] and [branch04code]. You make sure this has the correct data from excel. Then, you use that to join one copy of customers for each branch, and maybe use a case to figure differences:

select
     p.*
    ,stats_query.records_match
from
    pairs p
    inner join customers b2 on p.branch02code = b2.cust_code
    inner join customers b4 on p.branch04code = b4.cust_code
    cross apply
    (select case
            when b2.post_code = b4.post_code and b2.email= b4.email and b2.tel=b4.tel and ......
                then 1
            else 0
        end as records_match
    ) as stats_query
order by stats_query.records_match desc