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/

Advertisements