most recent promotion date to Manager, Sql Server

Language: Layout:

create table t(Job_Title varchar(25),Job_Description varchar(255),Name varchar(255), Effective_Date date)
Insert Into t values
('DM1',            'Manager'            ,'John Doe'        ,'2016/12/01'),
('AM1',            'Manager'            ,'John Doe'        ,'2015/01/12'),
('ASM',            'Assist Manager'       ,'John Doe'      ,'2014/10/01'),
('MG1',            'Manager'              ,'John Doe'      ,'2014/07/15'),    
('ASM',            'Assist Manager'       ,'John Doe'      ,'2012/03/01'),
('CSV',            'Service Rep'          ,'John Doe'      ,'2010/11/21'),
('DM1',            'Manager'              ,'Kris Vik'      ,'2014/11/21'),
('AM1',            'Manager'              ,'Kris Vik'      ,'2015/01/01'),
('CS2',            'Assist Manager'       ,'Kris Vik'      ,'2016/11/09'),
('CA1',            'Manager'              ,'Kris Vik'      ,'2017/01/14'),
('CSV',            'Service Rep'          ,'Kris Vik'      ,'2017/05/10')
;

select Job_Title,Job_Description,Name,Effective_Date 
from (select t.*
      ,min(case when job_description = 'Manager' then grp end) over(partition by name) as min_mgr_grp
      ,min(effective_date) over(partition by name,grp) as start_date
      from (select t.*
            ,row_number() over(partition by name order by effective_date desc) 
            - row_number() over(partition by name order by job_description,effective_date desc) as grp
            from t
            ) t
     ) t
where job_description = 'Manager' and grp = min_mgr_grp and effective_date = start_Date  
;

View schema
Execution time: 0,02 sec, rows selected: 2, rows affected: 11, absolute service time: 0,16 sec