T-SQL function to Get Maximum of values from the same row

Based on the ScottPletcher   solution from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html
–SELECT dbo.GetMax(23, 45, 64, 22, 18, 224, 74, 138, 1, 98, 11, 86, 198)
–Naturally adjust data type to match what you actually need for your specific values
I’ve created a set of functions (e.g. GetMaxOfDates3 , GetMaxOfDates13 )to find max of up to 13 Date values.

CREATE FUNCTION
GetMaxOfDates13(@value01 DateTime = NULL, @value02 DateTime = NULL,
                @value03 DateTime = NULL, @value04 DateTime = NULL,
                @value05 DateTime = NULL, @value06 DateTime = NULL,
                @value07 DateTime = NULL, @value08 DateTime = NULL,
                @value09 DateTime = NULL, @value10 DateTime = NULL,
                @value11 DateTime = NULL, @value12 DateTime = NULL,
                @value13 DateTime = NULL) RETURNS DateTime AS BEGIN
    RETURN(SELECT TOP 1 value FROM(
        SELECT @value01 AS value UNION ALL SELECT @value02 UNION ALL SELECT
            @value03 UNION ALL SELECT @value04 UNION ALL SELECT @value05 UNION
                ALL SELECT @value06 UNION ALL SELECT @value07 UNION ALL SELECT
                    @value08 UNION ALL SELECT @value09 UNION ALL SELECT @value10
                        UNION ALL SELECT @value11 UNION ALL SELECT @value12
                            UNION ALL SELECT @value13)
               AS[values] ORDER BY value DESC) END-- FUNCTION GO CREATE FUNCTION
    GetMaxOfDates3(@value01 DateTime = NULL, @value02 DateTime = NULL,
                   @value03 DateTime = NULL)

RETURNS DateTime
AS
BEGIN
RETURN dbo.GetMaxOfDates13(@value01,@value02,@value03,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END --FUNCTION

I haven’t considered UNPIVOT solution at the time of writing these functions, but it probably will be better.

Other solutions can be found at http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns

http://www.sqlrecipes.com/sql_questions_answers/find_minimum_maximum_value_across_several_columns-11/

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/007764d0-4a2d-4227-a4db-21fce471fbb3/

posted @ Monday, July 11, 2011 7:34 AM