You've been around long enough to know that's not the way to post sample data and expected results.  If you need a refresher, look in my signature.

Drew

000I notice you sidestepped the question about the two temp tables.  Here is a method.  The first CTE is simply to combine your two temp tables into one.

You can't group by an XML column.  The best that you can do is convert it to a (N)(VAR)CHAR column, which you can group by.

Drew

I think that the problem is that you are expecting NULL = NULL to be true when it's actually UNKNOWN.  I think what you want for the final join is...

I think you're looking for something like this.

WITH data_compare AS
(
 SELECT *
 , LAG(merchantname) OVER(PARTITION BY merchantnumber ORDER BY rec_start_date) AS prev_merchant_name
 , LAG(activeflag) OVER(PARTITION BY merchantnumber...