您可以按日期限制筛选，然后按日期以降序方式排序，最后只检索第一行。
例如：

select *
from t
where end <= date '2021-12-31'
order by end desc
fetch next 1 rows only

结果：

ID  START       END
--  ----------  ----------
 2  2021-09-13  2021-12-31

请参阅db<>fiddle上的运行示例。

试试看：

SELECT id, Start, END
FROM
(
SELECT *
, ROW_NUMBER () OVER (PARTITION BY ID ORDER BY Start DESC) AS RN_
FROM
(
VALUES
  (1, '2021-01-13', '2021-04-15')
, (1, '2021-04-16', '2021-11-28')
, (1, '2021-11-29', null)
, (2, '2021-05-05', '2021-09-13')
, (2, '2021-09-13', '2021-12-31')
, (3, '2020-01-09', '2021-08-29')
, (3, '2021-08-30', '2023-04-15')
) T (id, Start, END)
) T
WHERE RN_ = 1 AND END <= '2021-12-31'

dbfiddle link