Find max and min date over multiple fields of the same record

Question

I have a table with 4 distinct data-type fields. For each record, I need to find the max and min date over them.

That's easy to do for a set of records over a unique field. But how to do it in my case, without using cursor?

I'd need some sort of function that receives a list of fields, like max(field1, field2, field3, field4).

Answer 1

If I understood you correctly, I'd use CROSS APPLY with VALUES to "unpivot" your fields and then a standard MIN and MAX can be used. Something like this:

DECLARE @T TABLE (ID int IDENTITY, f1 int, f2 int, f3 int, f4 int);

INSERT INTO @T (f1, f2, f3,f4) VALUES
(1, 2, 3, 4),
(5, 6, 7, 8),
(7, 8, 0, 1),
(2, 3, 6, 5);

SELECT ID, MIN(f) AS MinF, MAX(f) AS MaxF
FROM
    @T
    CROSS APPLY
    (
        VALUES (f1), (f2), (f3), (f4)
    ) AS CA(f)
GROUP BY ID
ORDER BY ID;

Or without CROSS APPLY:

SELECT 
    ID
    ,(SELECT MIN(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MinF
    ,(SELECT MAX(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MaxF
FROM @T
ORDER BY ID;

One more variation of the same theme. Aggregate within CROSS APPLY.

SELECT ID, MinF, MaxF
FROM
    @T
    CROSS APPLY
    (
        SELECT MIN(f) AS MinF, MAX(f) AS MaxF
        FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)
    ) AS CA
ORDER BY ID;

SQL Fiddle

All variants return the same result

+----+------+------+
| ID | MinF | MaxF |
+----+------+------+
|  1 |    1 |    4 |
|  2 |    5 |    8 |
|  3 |    0 |    8 |
|  4 |    2 |    6 |
+----+------+------+

The last variant has a better-looking execution plan for this simple table.

Answer 2

SQL: Use this function to get least date between maximum of four dates, if you want to find the minimum for two columns just pass null for date3 and date4.

--Use this function to get least date 
--SELECT dbo.GetLeastDate('2021-01-19 01:09:28.997', '2021-01-19 01:07:28.997', '2021-01-19 00:02:28.997', '2021-01-19 02:05:28.997') as theDate  
--SELECT dbo.GetLeastDate('2021-01-19 01:09:28.997', '2021-01-19 01:07:28.997', '2021-01-19 02:05:28.997', null) as theDate  
--SELECT dbo.GetLeastDate('2021-01-19 01:09:28.997', '2021-01-19 01:07:28.997', null, null) as theDate  
--SELECT dbo.GetLeastDate('2021-01-19 01:09:28.997', null, null, null) as theDate  
CREATE OR ALTER FUNCTION [dbo].[GetLeastDate]
(
    @d1 datetime,
    @d2 datetime,
    @d3 datetime,
    @d4 datetime
)
RETURNS datetime
AS
BEGIN
    DECLARE @least datetime

    IF @d1 is null
       and @d2 is null
       and @d3 is null
        RETURN null

    Set @d1 = Isnull(@d1, getDate())
    Set @d2 = Isnull(@d2, getDate())
    Set @d3 = Isnull(@d3, getDate())
    Set @d4 = Isnull(@d4, getDate())
    
    IF @d1 < @d2 and @d1 < @d3 and @d1 < @d4
        SET @least = @d1
    ELSE IF @d2 < @d1 and @d2 < @d3 and @d2 < @d4
        SET @least = @d2
    ELSE IF @d3 < @d1 and @d3 < @d2 and @d3 < @d4
        SET @least = @d3
    ELSE
        SET @least = @d4

    RETURN @least
END