SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

5 rows affected

with cte as
(
  select id, col1, col2, load_date, change = null
  from   latest
  union all
  select id, col1, col2, load_date, change = null
  from   history
  union all
  select id, col1, col2, load_date = dateadd(day, 1, load_date),
         change = 'no show'
  from   (
             select id, col1, col2, load_date,
                    rn = row_number() over (partition by id order by load_date desc)
             from history 
         ) h
  where  h.rn = 1
  and    not exists
         (
             select *
             from   latest x
             where  x.id = h.id
         )
),
cte2 as
(  
  select id, col1, col2, load_date, change, 
         prev_col1 = lag(col1) over (partition by id order by load_date),
         prev_col2 = lag(col2) over (partition by id order by load_date)  
  from   cte
)
select *,
       change = isnull(change, '')
              + case when prev_col1 is null and prev_col2 is null
                     then 'new entry'
                     else ''
                     end
              + case when prev_col1 <> col1 
                     then 'col1 changed '
                     else ''
                     end
              + case when prev_col2 <> col2 
                     then 'col2 changed '
                     else ''
                     end  
from   cte2
order by id, load_date

id	col1	col2	load_date	change	prev_col1	prev_col2	change
1001	a	b	2024-01-01	null	null	null	new entry
1001	a	g	2024-01-02	null	a	b	col2 changed
1001	a	g	2024-01-03	null	a	g
1002	d	e	2024-01-01	null	null	null	new entry
1002	d	e	2024-01-02	no show	d	e	no show
1003	q	r	2024-01-03	null	null	null	new entry