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
 FROM ADMITS
)
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

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	AUTH_EFF_DATE	AUTH_EXP_DATE	MEM_EFF_DATE	MEM_EXP_DATE	PrevExprDate	StartNoteligible	EndNoteligible
118206307	2023-01-02	2023-06-07	2022-04-22	2023-09-30	null	null	2022-04-21
118206307	2023-08-30	2024-02-17	2022-04-22	2023-09-30	2023-09-30	2023-10-01	2022-04-21
118206307	2023-01-02	2023-06-07	2023-10-20	9999-12-31	2023-09-30	2023-10-01	2023-10-19
118206307	2023-08-30	2024-02-17	2023-10-20	9999-12-31	9999-12-31	9999-12-31	2023-10-19