SQL Server 2017

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

create table #temp (    [Store] int,    [EmpNo] int,    [Name] varchar(11),     [Jobcode] int,  [Dept] int,     [JobTitle] varchar(8),  [LastHireDate] date,    [Status] varchar(1),    [TerminationDate] date); 
Insert into #temp values (  119,    5042105,    'Gary D.',  10721,  10,     'Director',     '7/7/2003',     'T',    '1/18/2015'); 
Insert into #temp values (  119,    5391105,    'Sonia H.',     10721,  10,     'Director',     '12/19/2008',   'A',    NULL); 
Insert into #temp values (  119,    8155608,    'Paul W.',  10721,  10,     'Director',     '3/20/2017',    'T',    '11/30/2017'); 
Insert into #temp values (  119,    11952311,   'LARRY B.',     10721,  11,     'Director',     '4/15/2010',    'T',    '3/14/2012'); 
Insert into #temp values (  119,    19065019,   'Gary D.',  10721,  10,     'Director',     '7/7/2003',     'T',    '3/24/2017'); 
Insert into #temp values (  119,    19073019,   'Timothy P.',   10721,  10,     'Director',     '4/30/2013',    'T',    '12/5/2017'); 
Insert into #temp values (  119,    27230127,   'Jeffery F.',   10721,  10,     'Director',     '1/17/2010',    'T',    '12/21/2015'); 
Insert into #temp values (  119,    89113289,   'Timothy S.',   10721,  10,     'Director',     '8/3/2015',     'T',    '5/14/2019'); 
Insert into #temp values (  119,    89209289,   'Michael B.',   10721,  10,     'Director',     '12/17/2015',   'A',    NULL); 
Insert into #temp values (  119,    89453589,   'Harold H.',    10721,  10,     'Director',     '2/21/2018',    'T',    '5/7/2019'); 
Insert into #temp values (  119,    89604489,   'Jason B.',     10721,  10,     'Director',     '5/17/2017',    'A',    NULL); 
Insert into #temp values (  119,    89931089,   'Jeffery F.',   10721,  10,     'Director',     '1/17/2010',    'A',    NULL); 
Insert into #temp values (  119,    99371499,   'William A.',   10721,  10,     'Director',     '11/2/1998',    'A',    NULL); 
Insert into #temp values (  119,    99728099,   'K. Renee H.',  10721,  10,     'Director',     '9/11/1989',    'T',    '3/24/2017'); 
Insert into #temp values (  517,    11263511,   'Michael D.',   10061,  3,  'Manager',  '1/19/2015',    'T',    '7/27/2015'); 
Insert into #temp values (  517,    11544211,   'Richard L.',   10061,  3,  'Manager',  '10/10/2005',   'T',    '12/14/2014'); 
Insert into #temp values (  536,    3507003,    'Jeffrey S.',   10061,  3,  'Manager',  '2/18/2002',    'T',    '6/8/2012'); 
Insert into #temp values (  536,    12558412,   'John S.',  10061,  3,  'Manager',  '9/27/2010',    'A',    NULL); 
18 rows affected

select * from #temp;


Store
EmpNo
Name
Jobcode
Dept
JobTitle
LastHireDate
Status
TerminationDate


119
5042105
Gary D.
10721
10
Director
07/07/2003 00:00:00
T
18/01/2015 00:00:00

119
5391105
Sonia H.
10721
10
Director
19/12/2008 00:00:00
A
null

119
8155608
Paul W.
10721
10
Director
20/03/2017 00:00:00
T
30/11/2017 00:00:00

119
11952311
LARRY B.
10721
11
Director
15/04/2010 00:00:00
T
14/03/2012 00:00:00

119
19065019
Gary D.
10721
10
Director
07/07/2003 00:00:00
T
24/03/2017 00:00:00

119
19073019
Timothy P.
10721
10
Director
30/04/2013 00:00:00
T
05/12/2017 00:00:00

119
27230127
Jeffery F.
10721
10
Director
17/01/2010 00:00:00
T
21/12/2015 00:00:00

119
89113289
Timothy S.
10721
10
Director
03/08/2015 00:00:00
T
14/05/2019 00:00:00

119
89209289
Michael B.
10721
10
Director
17/12/2015 00:00:00
A
null

119
89453589
Harold H.
10721
10
Director
21/02/2018 00:00:00
T
07/05/2019 00:00:00

119
89604489
Jason B.
10721
10
Director
17/05/2017 00:00:00
A
null

119
89931089
Jeffery F.
10721
10
Director
17/01/2010 00:00:00
A
null

119
99371499
William A.
10721
10
Director
02/11/1998 00:00:00
A
null

119
99728099
K. Renee H.
10721
10
Director
11/09/1989 00:00:00
T
24/03/2017 00:00:00

517
11263511
Michael D.
10061
3
Manager
19/01/2015 00:00:00
T
27/07/2015 00:00:00

517
11544211
Richard L.
10061
3
Manager
10/10/2005 00:00:00
T
14/12/2014 00:00:00

536
3507003
Jeffrey S.
10061
3
Manager
18/02/2002 00:00:00
T
08/06/2012 00:00:00

536
12558412
John S.
10061
3
Manager
27/09/2010 00:00:00
A
null


select store, jobcode, case when status = 'A' then name end employeeName
from #temp t
where t.LastHireDate = (
    select max(t1.LastHireDate) from #temp t1 where t1.store = t.store and t1.jobcode = t.jobcode
)
order by store, jobcode
;


store
jobcode
employeeName


119
10721
null

517
10061
null

536
10061
John S.

Store	EmpNo	Name	Jobcode	Dept	JobTitle	LastHireDate	Status	TerminationDate
119	5042105	Gary D.	10721	10	Director	07/07/2003 00:00:00	T	18/01/2015 00:00:00
119	5391105	Sonia H.	10721	10	Director	19/12/2008 00:00:00	A	null
119	8155608	Paul W.	10721	10	Director	20/03/2017 00:00:00	T	30/11/2017 00:00:00
119	11952311	LARRY B.	10721	11	Director	15/04/2010 00:00:00	T	14/03/2012 00:00:00
119	19065019	Gary D.	10721	10	Director	07/07/2003 00:00:00	T	24/03/2017 00:00:00
119	19073019	Timothy P.	10721	10	Director	30/04/2013 00:00:00	T	05/12/2017 00:00:00
119	27230127	Jeffery F.	10721	10	Director	17/01/2010 00:00:00	T	21/12/2015 00:00:00
119	89113289	Timothy S.	10721	10	Director	03/08/2015 00:00:00	T	14/05/2019 00:00:00
119	89209289	Michael B.	10721	10	Director	17/12/2015 00:00:00	A	null
119	89453589	Harold H.	10721	10	Director	21/02/2018 00:00:00	T	07/05/2019 00:00:00
119	89604489	Jason B.	10721	10	Director	17/05/2017 00:00:00	A	null
119	89931089	Jeffery F.	10721	10	Director	17/01/2010 00:00:00	A	null
119	99371499	William A.	10721	10	Director	02/11/1998 00:00:00	A	null
119	99728099	K. Renee H.	10721	10	Director	11/09/1989 00:00:00	T	24/03/2017 00:00:00
517	11263511	Michael D.	10061	3	Manager	19/01/2015 00:00:00	T	27/07/2015 00:00:00
517	11544211	Richard L.	10061	3	Manager	10/10/2005 00:00:00	T	14/12/2014 00:00:00
536	3507003	Jeffrey S.	10061	3	Manager	18/02/2002 00:00:00	T	08/06/2012 00:00:00
536	12558412	John S.	10061	3	Manager	27/09/2010 00:00:00	A	null