It's not clear from your question if your history table is a normal table that you maintain or is a system-versioned temporal table - if not then perhaps it should be where you can use FOR SYSTEM_TIME to query it?

To get the desired result above you can simply use a correlated subquery:

select 
  id, state, (
    select top (1) valid_to
    from @history_table ht
    where ht.id = ct.id 
      and ct.state is not null
      and (ht.state != ct.state or ht.state is null)
    order by valid_to desc
  ),
  other_state
from @current_table ct;

For performance you'd want the history_table to have an index on id, valid_from .

If you did want to also get the values of other columns you would instead implement in an apply - although it's not really clear from your data or your description if the newest row in the history_table is the previous row of the current_table or not (it should be).

Edit

With some more info about your data I think a different approach is called for. I still think you should be implementing system-versioning here as having a history table that also contains the current row makes things harder.

Another approach would be to use the earliest valid_from date from the most recent block of matching states. A view on the history table can provide a grouped sequence, then your query can make use of the view instead:

create view history_groups as
with d as (
  select *, case when Lag(state) over(partition by id order by valid_from desc) = state then 0 else 1 end diff
  from dbo.history_table
)
select *, Sum(diff) over(partition by id order by valid_from desc) groupNo
from d;

select 
  id, state, (
    select top (1) valid_from
    from history_groups h
    where h.id = ct.id 
      and ct.state is not null
      and h.groupNo = 1
    order by h.valid_from
  ),
  other_state
from current_table ct;

data

create table current_table ( 
id int, 
state varchar(10),
other_state  varchar(10))
INSERT INTO current_table
(id,state,other_state)
VALUES 
(1, 'green' ,'aaa'),
(2, 'red','aaa'),
(3, null,'aaa')

create table history_table ( 
history_id int,
id int, 
state varchar(10),
other_state  varchar(10) ,
valid_from  date, 
valid_to  date)
INSERT INTO history_table
(history_id,id,state,other_state,valid_to)
VALUES 
(8, 1, 'green' ,'aaa', '2023-06-18'   , '9999-12-31'),
(3, 1, null,'aaa', '2023-06-01', '2023-06-18'),
(7, 2, 'red','aaa', '2023-06-16', '9999-12-31'),
(6, 2, 'red','bbb', '2023-06-10', '2023-06-16'),
(2, 2, null,'aaa', '2023-06-01', '2023-06-10'),
(5, 3, null,'aaa', '2023-06-14', '9999-12-31'),
(4, 3, 'blue','aaa', '2023-06-10', '2023-06-14'),
(1, 3, null,'aaa', '2023-06-01', '2023-06-10');

you should use aggregate functionmax function inside window functionRow_Number in CTE and to distinguish your values and use JOIN missing NULL values

with t as (
select  
c.id,
c.state, 
max(valid_from) valid_from ,
row_number() over (partition by c.id order by  max(valid_from) asc) idn,
h.other_state 
from current_table c
join history_table h
on c.id=h.id and 
(c.state=h.state or c.state is NULL and h.state is NULL )
group by c.id,c.state,h.other_state
)

select id,state,valid_from,other_state from  t
where idn=1
)

dbfiddle

I adapted the solution of @stu and @MatBailie with a groupby approach:

WITH grps
AS (
    SELECT id
        ,STATE
        ,valid_from
        ,valid_to
        ,Row_number() OVER (
            PARTITION BY id ORDER BY valid_from DESC
            ) - Row_number() OVER (
            PARTITION BY STATE
            ,id ORDER BY valid_from DESC
            ) AS g
    FROM dbo.history_table
    )
SELECT id
    ,STATE
    ,(
        SELECT min(valid_from) AS valid_from
        FROM grps
        WHERE g = 0
            AND ct.id = id
            AND ct.STATE IS NOT NULL
        GROUP BY id
        ) AS modified
    ,other_state
FROM current_table ct

dbfiddle

This approach does not consider the case that an entry in the current table is inserted, deleted, and inserted again, so that the valid_to of one column is not the same value as the valid_from row of the next row in the history table.

However, on our table with 5k ids and history table with approx. 200k entries, this query is significantly faster.