Capturing difference between 2 tables in one table

I understand my mistake with creating questions in StackOverflow, so I want to rewrite my question.

I have two tables. One of them is my target table with old data and the source table with new data. Implicitly I want to compare these 2 tables and get information on which columns have been changed and changed status.

So I created new temporary tables as below:

create table #olddata (
id int,
status1 varchar(50),
status2 varchar(50),
status3 varchar(50),
)

create table #newdata (
id int,
status1 varchar(50),
status2 varchar(50),
status3 varchar(50),
)

--data inside are examples
insert into #olddata
SELECT 1 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 union all
SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
SELECT 3 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 

insert into #newdata
SELECT 1 as id , 'open' as status1 ,'closed' as status2, 'open' as status3 union all
SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
SELECT 3 as id , 'open' as status1 ,'open' as status2, 'open' as status3

I want to capture changes, where changes are called “closed” and insert getdate() in these changes. But I can’t, I tried using except statement to get information on which columns have been changed:

--My data below shows changed
with differencescte as (
select * from #newdata
except
select * from #olddata)

--select * from differencescte

,unpivotdiff as (
select ID,Statuss,ChangedStatus
from differencescte
UNPIVOT
(
    ChangedStatus for Statuss In (status1,status2,status3)
) as up
)

select * from unpivotdiff
where ChangedStatus <> 'open'

My final step shows ID and status, but how to insert the date of change in except statement to get a table as below? ID|Statuss|ChangedStatus|Dateofchanged|

Answer

here is one approach

    drop table if exists #expected
    create table #expected (
    id int,
    changedcolumn varchar(50),
    dateofchange datetime);
    
    
    with target_t as(
    SELECT 1 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 union all
    SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
    SELECT 3 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 )
    
    ,source_t as (
    SELECT 1 as id , 'open' as status1 ,'closed' as status2, 'open' as status3 union all
    SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
    SELECT 3 as id , 'open' as status1 ,'open' as status2, 'open' as status3 )
    
    
 INSERT INTO #expected
SELECT tb.*
FROM(
    SELECT 
        t.id
        ,'Status1' as changedcolumn
        ,getdate() AS dateofchange
    FROM target_t t
    INNER JOIN source_t s
        ON t.id = s.id
    WHERE ISNULL(t.status1,'') <> ISNULL(s.status1,'')
union all
    SELECT 
        t.id
        ,'Status2' as changedcolumn
        ,getdate() AS dateofchange
    FROM target_t t
    INNER JOIN source_t s
        ON t.id = s.id
    WHERE ISNULL(t.status2,'') <> ISNULL(s.status2,'')
union all
    SELECT 
        t.id
        ,'Status3' as changedcolumn
        ,getdate() AS dateofchange
    FROM target_t t
    INNER JOIN source_t s
        ON t.id = s.id
    WHERE ISNULL(t.status3,'') <> ISNULL(s.status3,'')
    ) tb