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)

I was confused on the syntax of `VA(LDATE)` hence wasn't sure what to put in a WHERE clause. What is LDATE? — redOctober13, Nov 15 '22 at 18:39
LDATE is the column alias name; Having 50 date columns is your root problem, if these were *rows* you wouldn't need any pivoting of columns into rows at all. — Stu, Nov 15 '22 at 18:40

score 2 · Accepted Answer · answered Nov 15 '22 at 18:38

2

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

answered Nov 15 '22 at 18:38

GMB

216,147
25
84
135

Less than `getdate()` I guess, but I'll try. Any clue whether CROSS APPLY would be better/worse than UNPIVOT (a keyword I only just learned today)? – redOctober13 Nov 15 '22 at 18:40
1

I would suggest ignoring the pivot/unpivot syntax, it's syntactic sugar that's quite restrictive and, imho, not particularly intuitive. – Stu Nov 15 '22 at 18:43

score 2 · Answer 2 · answered Nov 15 '22 at 18:40

2

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

answered Nov 15 '22 at 18:40

John Cappelletti

79,615
7
44
66

Could you do `WHERE [Key] Like ...` instead? I have about 200 columns in the table, so listing 50 to check is better than listing 150 to exclude. – redOctober13 Nov 15 '22 at 19:03
@redOctober13 Absolutely. That said, 200 columns is a bit much... performance wise you may be better off enumerating the columns is the CROSS APPLY as GMB suggests. But try it. The performance may surprise you – John Cappelletti Nov 15 '22 at 19:09
It did seem to run quite a bit slower. I used `Where Left([Key],7) = 'G_DATE_` – redOctober13 Nov 15 '22 at 19:12
1

@redOctober13 I would have gone with WHERE [Key] like 'G_DATE%' but I would expect a performance hit considering 150 extra columns are being processed – John Cappelletti Nov 15 '22 at 19:15

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

2 Answers2