add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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.