CODEWITHSUNDEEP
sqlsql-server
Serii
Serii

Reputation: 15

Comparing 2 tables and trace the changed columns and other set to null

I have situation when comparind 2 tables. One is current data and other is a snapshot of previous the data. The result of the comparison is stored into a #temp table ( the row which was changed). After that I am comparing #temp table to the last snapshot data to trace changed columns. I need For this I did a select statement :

I am thinking to add and ROW_NUMBER() windows function and may this will solve this. Any suggestions?

Upvotes: 0

Views: 148

Answers (2)

SteveC
SteveC

Reputation: 6015

There appears to be NULL comparisons within aggregation. SQL Server's built-in aggregate functions ignore NULLs. To test for any NULL values the query checks sum(case when [ADDRESS1] is null then 1 else 0 end)=0, etc. Something like this

with nullif_cte as (
    select l.[ADDRESSID],
           nullif(l.[ADDRESS1], n.[ADDRESS1]) ADDRESS1,
           nullif(l.[ADDRESS2], n.[ADDRESS2]) ADDRESS2,
           nullif(l.[ADDRESS3], n.[ADDRESS3]) ADDRESS3,
           nullif(l.[ADDRESS4], n.[ADDRESS4]) ADDRESS4,
           nullif(l.[CITY], n.[CITY]) [CITY],
           nullif(l.[CLIENTID], n.[CLIENTID]) [CLIENTID],
           nullif(l.[COUNTRY_CODE], n.[COUNTRY_CODE]) [COUNTRY_CODE],
           nullif(l.[POST_CODE], n.[POST_CODE]) [POST_CODE]
    from #tempUpdatedAddress l 
         left join UpdatedEntityStg n ON n.[ADDRESSID] = n.[ADDRESSID])
select [ADDRESSID],
       case when sum(case when [ADDRESS1] is null then 1 else 0 end)=0 
                          then max([ADDRESS1]) else null end [ADDRESS1],
       case when sum(case when [ADDRESS2] is null then 1 else 0 end)=0 
                          then max([ADDRESS2]) else null end [ADDRESS2],
       case when sum(case when [ADDRESS3] is null then 1 else 0 end)=0 
                          then max([ADDRESS3]) else null end [ADDRESS3],
       case when sum(case when [ADDRESS4] is null then 1 else 0 end)=0 
                          then max([ADDRESS4]) else null end [ADDRESS4],
       case when sum(case when [CITY] is null then 1 else 0 end)=0 
                          then max([CITY]) else null end [CITY],
       case when sum(case when [CLIENTID] is null then 1 else 0 end)=0 
                          then max([CLIENTID]) else null end [CLIENTID],
       case when sum(case when [COUNTRY_CODE] is null then 1 else 0 end)=0 
                          then max([COUNTRY_CODE]) else null end [COUNTRY_CODE],
       case when sum(case when [POST_CODE] is null then 1 else 0 end)=0 
                          then max([POST_CODE]) else null end [POST_CODE]
from nullif_cte nc
group by [ADDRESSID];

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If I understand correctly, you can use row_number() or outer apply. The latter changes the FROM clause to:

FROM #tempUpdatedAddress l OUTER APPLY
     (SELECT TOP (1) n.*
      FROM UpdatedEntityStg n 
      WHERE n.[ADDRESSID] = n.[ADDRESSID] 
      ORDER BY n.snapshotdate DESC
     ) n

Note: You don't specify how you know the ordering of the snapshots, so I invented snapshotdate for the ORDER BY. Use whatever column contains the ordering information.

Upvotes: 1

Related Questions