I need to get the difference between two tables.
I need to compare Product, Qty & price columns from two tables and say if its new record or I need to mention which column value is changed.
Example Table A
Product | Qty | Price | Comments
A 20 500 xyz
B 50 200 xyz
C 90 100 abc
Example Table B
Product | Qty | Price | Comments
A 20 500 sd
B 70 200 cv
C 90 200 wsd
D 50 500 xyz
Currently I am using Expect which gives all new / mismatched rows.
select Product,Qty,Price
from TableB
except
select Product,Qty,Price
from TableA
Product | Qty | Price
B 70 200
C 90 200
D 50 500
But I need the result set like below
Product | Result
B Updated Qty
C Updated Price
D New
You can do this using LEFT JOIN:
SELECT b.Product,
b.Qty,
b.Price,
Result = CASE WHEN a.product IS NULL THEN 'New'
ELSE 'Updated: ' +
STUFF( CASE WHEN a.Qty != b.Qty THEN ',Qty' ELSE '' END +
CASE WHEN a.Price != b.Price THEN ',Price' ELSE '' END,
1, 1, '')
END
FROM TableB b
LEFT JOIN TableA a
ON a.Product = b.Product
WHERE a.Product IS NULL
OR a.Qty != b.Qty
OR a.Price != b.Price;
Example on SQL Fiddle
Not the most concise approach for sure, but readable and probably efficient:
SELECT B.Product,
Result = 'Updated Qty'
FROM TableB B
LEFT OUTER JOIN TableA A
ON B.Product = A.Product
WHERE A.Product IS NOT NULL
AND A.Qty <> B.Qty
AND A.Price = B.Price
UNION ALL
SELECT B.Product,
Result = 'Updated Price'
FROM TableB B
LEFT OUTER JOIN TableA A
ON B.Product = A.Product
WHERE A.Product IS NOT NULL
AND A.Price <> B.Price
AND A.Qty = B.Qty
UNION ALL
SELECT B.Product,
Result = 'Updated Qty and Price'
FROM TableB B
LEFT OUTER JOIN TableA A
ON B.Product = A.Product
WHERE A.Product IS NOT NULL
AND A.Price <> B.Price
AND A.Qty <> B.Qty
UNION ALL
SELECT B.Product,
Result = 'New'
FROM TableB B
LEFT OUTER JOIN TableA A
ON B.Product = A.Product
WHERE A.Product IS NULL
Demo
If you need to order the result you have to do that in an outer query like here.
