I’m looking for a pair of SQL functions (MS SQL Server) that will return the MAX or MIN of a list of dates

I’m looking for a pair of SQL functions that will return the MAX and MIN value of a list of dates (or, alternatively, the MAX and MIN of two dates – I can then stack the calls to handle the whole list). I can’t use the MAX() and MIN() functions (as far as I can tell) because that will return the maximum or minimum value of a particular column. What I need is the maximum or minimum column for a particular row.

Here is some sample data to illustrate what I’m looking for:

Current query results:

MyID    Date1       Date2       Date3       Date4
---------------------------------------------------------
ROW1    1/1/2019    4/23/2020   12/4/1980   5/2/2020
ROW2    6/3/2020    1/1/2020    5/3/2021    11/9/1998
ROW3    8/15/1980   7/4/2019    12/1/2030   1/2/2020

Desired query results:

MyID    MaxDate     MinDate
---------------------------------
ROW1    12/4/1980   5/2/2020
ROW2    11/9/1998   5/3/2021    
ROW3    8/15/1980   12/1/2030

(Here, I have 4 different date columns. In my actual situation, I will need to sift through 8 different columns.)

Ideally, I’d like to be able to do something like this:

SELECT
    MyID,
    MIN(Date1, Date2, Date3, Date4),
    MAX(Date1, Date2, Date3, Date4)
FROM ...

Or, if necessary, I could do something like this:

SELECT
    MyID,
    MIN(Date1, MIN(Date2, MIN(Date3, Date4))),
    MAX(Date1, MAX(Date2, MAX(Date3, Date4)))
FROM ...

Obviously, the existing MIN and MAX functions don’t work this way. Is there an alternative function that does? I can probably construct some kind of CASE clause that will do this, but it seems like it would be very ugly… It may be a little cleaner to use nested IF statements, though still pretty ugly. I’ll start messing with that after I post this. I’m just hoping for a more elegant, cleaner solution.

Answer

A standard SQL solution is to UNION all your dates

SELECT MyID, MAX(thedate ) AS maxdate, MIN(thedate ) AS mindate
FROM
(
SELECT MyID, Date1 AS thedate FROM table
UNION ALL
SELECT MyID, Date2 AS thedate FROM table
UNION ALL 
SELECT MyID, Date3 AS thedate FROM table
UNION ALL
SELECT MyID, Date4 AS thedate FROM table
) T
GROUP BY MyID

There might be better other solutions with window functions, depending on your RDBMS, which you haven’t specified. But this one should work with any RDBMS.

Advertisement

Answer