CODEWITHSUNDEEP
sqlcomparefieldteradata
Rohan Hodarkar
Rohan Hodarkar

Reputation: 105

Compare teradata table fields and return the percentage by which the fields are matching or differing

Suppose I have 2 tables with same structure and I want to compare data in the tables. If for a particular field the data is same then i need a report that will say 100% (match) else i need the percentage by which the data is differing in the two columns.

NOTE: Need to take a join on unique key. (Assumption the joining key unique and not null)

Upvotes: 1

Views: 3434

Answers (3)

Rob Paller
Rob Paller

Reputation: 7786

I believe the following will provide you with the variance of B1.Value with respect to A1.Value when the unique key is present in both tables. I originally was going to write this with COALESCE() to account for missing records produced by the FULL OUTER JOIN but then I had to make an assumption about the domain and what would be a valid value to use for NULL. Instead, I elected to just show NULL if there is a missing record.

SELECT CASE WHEN A1.Value IS NULL OR B1.Value IS NULL
            THEN NULL
            WHEN A1.Value = B1.Value
            THEN 1
            ELSE ((A1.Value - B1.Value) / (A1.Value * 1.000)
       END AS Variance      
  FROM TableA A1
  FULL OUTER JOIN
       TableB B1
    ON A1.{UniqueKey} = B1.{UniqueKey};

NOTE: In order to account for the situation where the Value field does not have decimal precision I multiplied it by 1.00. Otherwise the result for Variance would be returned without decimal precision.

A simple WHERE clause can target missing records, records without a variance, or records with a variance.

Upvotes: 1

rogue-one
rogue-one

Reputation: 11587

I have asssumed two tables Table1 and Table2 and we are using Table1 as the reference table. Below is the query

SELECT
A.CNT as diff_number ,B.CNT as actual number
FROM
(SELECT COUNT(*) as cnt FROM
(SELECT * FROM TABLE1
MINUS
SELECT * FROM TABLE2) A1

UNION ALL

(SELECT * FROM TABLE2
MINUS
SELECT * FROM TABLE1) A2 ) A
CROSS JOIN
(SELECT COUNT(*) AS CNT1 FROM TABLE1) B

Upvotes: 1

Raj
Raj

Reputation: 22926

Do intersect of the two tables and compare the count with the total number of records with the either of the table. To find out the percentage, compare the matching count and total count.

SELECT 'Matching', COUNT(*) FROM (
SELECT COLUMN_1 FROM TABLE_1
INTERSECT
SELECT COLUMN_1 FROM TABLE_2 )
UNION ALL
SELECT 'Table_1', COUNT(*) FROM TABLE_1
UNION ALL
SELECT 'Table_2', COUNT(*) FROM TABLE_2

Suppose Table_1 has 20 records and Table_2 has 25 and there is a match of 10 values. You would get 10 while doing the intersect.

You can also try using MINUS instead of INTERSECT to find out the Not matching records.

Upvotes: 1

Related Questions