Find most recent (non-future) date from a list of columns

Question

Assume a table like this (in actuality I have 50 date columns to compare):

ID	MY_DATE_1	MY_DATE_2	MY_DATE 3
1	2022-10-1	2022-11-1	2022-12-1
2	2022-10-31	2022-11-31	2022-12-31

For each record, I want to get the most recent, non-blank past date (less than today's date) looking across all date columns.

So I would want these results (given a current date of 2022-11-15):

ID	LATEST_DATE
1	2022-11-1
2	2022-10-31

I found this code elsewhere, but it just gets the max date across columns, and I need to add the condition somewhere for "max past" and not just "max overall" but I'm not experienced with CROSS APPLY and don't know if I can modify this query or if there's another way to write it that will work.

SELECT MA.MaxDate
FROM <my_table> AS MT
CROSS APPLY (
 SELECT MAX(VA.LDate)
 FROM (VALUES(MT.MY_DATE_1),(MT.MY_DATE_2),(MT.MY_date_3)) VA(LDate)
) AS MA(MaxDate)

Answer 1

You can filter before aggregation. Here is one way to do it:

select mt.id, max(va.ldate) as maxdate
from my_table as mt
cross apply ( values (mt.my_date_1), (mt.my_date_2), (mt.my_date_3) ) va(ldate)
where va.ldate > getdate()
group by mt.id