1

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

4

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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.