Sorry for the delayed response. Got caught up a little bit at my work place. Here is a new approach to the problem. i have tested it with different scenarios...

Just with a very small change i was able to produce the result you wanted

select * from

(

select *,

row_number() over(partition by record_id, date_changed, new_state order by id) as...

I have used your #table

select * into #table1 from

(select record_id, date_changed, min(id) min_id, max(id) max_id, new_state

from #table B

group by record_id, date_changed, new_state) a

select * from

(

select *,

row_number()...