SQL Server 2022

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

-- Set date format to day/month/year.  
SET DATEFORMAT mdy;

CREATE TABLE ADMITS
(
ID_NUM INT
,AUTH_EFF_DATE  date null
,AUTH_EXP_DATE  date null
,MEM_EFF_DATE   date null
,MEM_EXP_DATE date null

)

INSERT INTO ADMITS (ID_NUM ,AUTH_EFF_DATE,AUTH_EXP_DATE,MEM_EFF_DATE,MEM_EXP_DATE)
VALUES
 (118206307, '1/2/2023', '6/7/2023', '4/22/2022', '9/30/2023')
,(118206307, '8/30/2023', '2/17/2024', '4/22/2022', '9/30/2023')
,(118206307, '1/2/2023', '6/7/2023', '10/20/2023', '12/31/9999')
,(118206307, '8/30/2023', '2/17/2024', '10/20/2023', '12/31/9999');

SELECT * FROM ADMITS;

WITH Previous as
(  
 SELECT *
  ,LAG(MEM_EXP_DATE) OVER(PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE,AUTH_EFF_DATE)  as PrevExprDate
  ,LAG(AUTH_EFF_DATE) OVER(PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE,AUTH_EFF_DATE)  as PrevAUTH_EFF_DATE
  ,LAG(AUTH_EXP_DATE) OVER(PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE,AUTH_EFF_DATE)  as PrevAUTH_EXP_DATE
 FROM ADMITS
),
  eligible as
(  
SELECT *
  ,  CASE 
     WHEN PrevExprDate IS NULL THEN NULL 
     WHEN PrevExprDate = '12/31/9999' THEN '12/31/9999'
     ELSE DateAdd(day, 1, PrevExprDate) END as StartNotEligible
  ,  CASE 
     WHEN MEM_EFF_DATE IS NULL THEN NULL 
     WHEN MEM_EFF_DATE = '12/31/9999' THEN '12/31/9999'
     ELSE DateAdd(day, -1, MEM_EFF_DATE) END as EndNotEligible
FROM Previous
)
Select ID_NUM, PrevAUTH_EFF_DATE, PrevAUTH_EXP_DATE, StartNotEligible,EndNotEligible 
FROM eligible
WHERE (StartNotEligible <= PrevAUTH_EXP_DATE      AND EndNotEligible >= PrevAUTH_EFF_DATE)

ID_NUM	AUTH_EFF_DATE	AUTH_EXP_DATE	MEM_EFF_DATE	MEM_EXP_DATE
118206307	2023-01-02	2023-06-07	2022-04-22	2023-09-30
118206307	2023-08-30	2024-02-17	2022-04-22	2023-09-30
118206307	2023-01-02	2023-06-07	2023-10-20	9999-12-31
118206307	2023-08-30	2024-02-17	2023-10-20	9999-12-31

ID_NUM	PrevAUTH_EFF_DATE	PrevAUTH_EXP_DATE	StartNotEligible	EndNotEligible
118206307	2023-08-30	2024-02-17	2023-10-01	2023-10-19