Need Help To Get Last Record Based On Column Value

1. Attach a row_number() that partitioned by key columns and ordered by last modified date to the data set as serials number.
2. Filter all rows have 'Active' status.
3. Get 'Inactive' rows which key columns match the 'Active' rows and sn equals to the 'Active' sn + 1.

Code is here:

CREATE TABLE #T
(
	C_ID int,
	C_Name varchar(10),
	[Active/Inactive] varchar(10),
	Last_Modified_Date datetime
);

INSERT INTO #T
VALUES
 (111, 'AAA', 'Active', '2024-04-22 19:00:51.863')
,(111, 'AAA', 'Inactive', '2024-04-21 19:00:51.863') --
,(111, 'AAA', 'Inactive', '2024-04-20 19:00:51.863')
,(111, 'AAA', 'Inactive', '2024-04-23 19:00:51.863')
;

WITH CTE
AS
(
	SELECT *
		,ROW_NUMBER() OVER(PARTITION BY C_ID, C_NAME ORDER BY Last_Modified_Date DESC) AS SN  --Without partition by will be OK
	FROM #T 
)
SELECT I.C_ID, I.C_Name, I.[Active/Inactive], I.Last_Modified_Date FROM CTE I
INNER JOIN
(
	SELECT * FROM CTE WHERE [Active/Inactive] = 'Active'
) A
ON I.C_ID = A.C_ID
AND I.C_Name = A.C_Name
AND I.SN = A.SN + 1
WHERE I.[Active/Inactive] = 'Inactive'

DROP TABLE #T