SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tblStaffOnCase(
	ID int,
	CaseID Int,
	Eff_Date Date,
	Thru_Date Date,
	WorkerID Int,
	SuperID Int,
	MngrID Int
);

insert into tblStaffOnCase values
(213,1,'06/01/2020','11/01/2020',1,2,3),
(980,1,'11/02/2020','03/15/2021',1,4,3),
(1325,1,'03/16/2021',NULL,6,2,3);

3 rows affected

declare @StartDate as date = '10/2/2020'
declare @EndDate as date = '9/30/2021'

SELECT *
FROM tblStaffOnCase 
WHERE ID = (
   SELECT Max(ID)
   FROM tblStaffOnCase
   WHERE Eff_Date >= @StartDate AND (Thru_Date <= @EndDate OR Thru_Date IS NULL)) 


ID
CaseID
Eff_Date
Thru_Date
WorkerID
SuperID
MngrID


1325
1
2021-03-16
null
6
2
3


declare @StartDate as date = '10/2/2020'
declare @EndDate as date = '9/30/2021'

select 
	ID,
	CaseID,
	Eff_Date,
	Thru_Date,
	WorkerID,
	SuperID,
	MngrID
from
(
	select 
		ID,
		CaseID,
		Eff_Date,
		Thru_Date,
		WorkerID,
		SuperID,
		MngrID,
		row_number() over(order by id desc) rn
   from tblStaffOnCase
   where Eff_Date >= @StartDate and (Thru_Date <= @EndDate or Thru_Date is null)
) x 
where rn=1


ID
CaseID
Eff_Date
Thru_Date
WorkerID
SuperID
MngrID


1325
1
2021-03-16
null
6
2
3