SQL creating a column for only the latest ID with an additional condition

I’m working with a SQL Server table where I have the following

ID   Date_Stamp   Stopper       ProcessStep  Age  
A1   01/01/2019   DO_NOT_USE    Complete
A1   31/12/2019   New           Enter
A2   02/01/2019   New           Enter        What is the age?
A3   06/01/2019   New           Enter    
A4   01/01/2019   New           Enter
A4   02/01/2019   New           Process
A4   03/01/2019   New           Review       What is the age? 
A5   17/01/2019   New           Enter    

Can anyone tell my how can I use SQL to add a column that calculates the age of the latest process for each ID, ignoring all ID’s where the Stopper says “DO_NOT_USE” please?

I also need the whole table

Answer

You can use row_number with not exists :

select t.*, 
       (case when seq = 1 and not exists (select 1 
                                          from table t1 
                                          where t1.id = t.id and t1.stopper = 'do_not_use'
                                         )
             then datediff(dd, Date_Stamp, GETDATE()) 
        end) as age
from (select t.*,
             row_number() over (partition by id order by date desc) as seq
      from table t
     ) t
order by id;

EDIT : Using window function :

select t.*, 
       (case when seq = 1 and all_cnt = stopper_chk 
             then datediff(dd, Date_Stamp, getdate()) 
        end) as age
from (select t.*, 
            row_number() over (partition by id order by date desc) as seq,
            count(case when t.stopper = 'do_not_use' then null else 1 end) over (partition by id) as stopper_chk, 
            count(*) over (partition by id) as all_cnt
      from table t
     ) t;