恩~是不知道你的日期會不會突然跳大的日期~不然B寫法簡單~

declare @A table(
	ID nvarchar(10)
	,Name nvarchar(10)
)
insert into @A
values('001','王小明')
,('002','張小花')
,('003','李小壯')
,('004','黃小美')

declare @B table(
	ID nvarchar(10)
	,[STATUS] nvarchar(10)
	,[DATE] DATE
)

insert into @B
values('001','N','2024-01-01')
,('001','N','2024-01-10')
,('001','Y','2024-01-28')
,('002','X','2024-03-10')
,('002','N','2024-03-25')
,('003','N','2024-10-05')
,('003','Y','2024-02-13')
,('004','N','2024-01-13')
,('004','R','2024-01-15')

--A寫法
select ID
,Name
,max(Max_Date) Max_Date
from (
	select a.ID
	,a.Name
	,isNull((
		select max( 
			case when c.[STATUS] = 'Y' 
			then [DATE]
			else null
			end
		)
		from @B c
		where A.ID = C.ID
	),[DATE]) Max_Date
	from @A a
	left join @B b on a.ID = b.ID
) k
group by ID
,Name


--B寫法
select a.ID
,a.Name
,max(b.Date) Max_Date
from @A a
left join @B b on a.ID = b.ID
group by a.ID
,a.Name

--C寫法
select a.ID
,a.Name
,isNull(
	max(case when b.[STATUS] = 'Y' then b.Date else null end)
	,max(b.[Date])
) Max_Date
from @A a
left join @B b on a.ID = b.ID
group by a.ID
,a.Name
order by a.ID

--C 寫法 改
SELECT 
    A.ID,
    A.NAME,
	CASE
	WHEN  (SELECT count(DATE) FROM #B B WHERE B.ID = A.ID AND B.STATUS = 'Y')>0
	THEN subquery.MAX_DATE
	ELSE  null
	END AS 'DATE'

FROM 
    #A A
CROSS APPLY
    (
        SELECT MAX(B.DATE) AS MAX_DATE
        FROM #B B
        WHERE B.ID = A.ID
    ) AS subquery;

SELECT * INTO #A FROM ( VALUES
   ('001',N'王小明')
  ,('002',N'張小花')
  ,('003',N'李小壯')
  ,('004',N'黃小美')
  ) AS A([ID],[NAME])
;

SELECT * INTO #B FROM ( VALUES
 ('001','N',CONVERT(DATE,'2024-01-01'))
,('001','N','2024-01-10')
,('001','Y','2024-01-28')
,('002','X','2024-03-10')
,('002','N','2024-03-25')
,('003','N','2024-02-05')
,('003','Y','2024-02-13')
,('004','N','2024-01-13')
,('004','R','2024-01-15')
) AS B([ID],[STATUS],[DATE])
;

SELECT * FROM #A;
SELECT * FROM #B;

-- version 1
WITH S AS (
 SELECT A.ID, A.NAME, B.[DATE]
 , ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY IIF(STATUS = 'Y',0,1),[DATE]) [SN]
 FROM #A A
 LEFT OUTER JOIN #B B ON A.ID = B.ID
)

SELECT S.ID, S.NAME, S.DATE
FROM S WHERE SN = 1
;

-- version 2 (沒Y留空)
SELECT A.ID, A.NAME, B.[DATE]
FROM #A A
LEFT OUTER JOIN #B B ON A.ID = B.ID AND B.[STATUS] = 'Y'
;

參考看看

SELECT 
	A.*  , 
	IIF(IsIncludeY > 0 , MaxDate , NULL) AS [Date]
FROM tblA AS A
	JOIN
		(
			SELECT ID ,
				SUM(IIF([Status] = 'Y' , 1 , 0)) AS IsIncludeY ,
				MAX([Date]) AS MaxDate
			FROM tblB
			GROUP BY ID
		) AS B ON A.ID = B.ID