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

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)`.

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.

• The cross apply worked, thanks a lot! I'm gonna try the subquery variant. Nov 5, 2015 at 14:57

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
``````