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

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)

CodePudding user response：

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

CodePudding user response：

If you don't want list all 50 date columns, you can use a bit of JSON to dynamically UNPIVOT your data without actually using Dynamic SQL

Example

Select A.ID
      ,B.* 
 From  YourTable A
 Cross Apply  (
                 Select Value = max(Value)
                  From  OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
                  Where [Key] not in ('ID','OtherColumns','ToExclude')
                    and try_convert(date,value)<=getdate()
              ) B

Results