| How to
find the maximum of two numbers? |
|
Execute the following T-SQL scripts in Management Studio Query Editor to demonstrate the creation and testing of a function to return the maximum of two numbers.
-- SQL maximum of two numbers - money data type USE AdventureWorks2008; GO -- T-SQL user-defined function - scalar function -- SQL calculate the maximum of two values - mssql max function CREATE FUNCTION fnMaxValue (@ColumnA MONEY, @ColumnB MONEY) RETURNS MONEY AS BEGIN RETURN (0.5 * ((@ColumnA + @ColumnB) + abs(@ColumnA - @ColumnB))) END GO -- Test user-defined function - UDF SELECT dbo.fnMaxValue (10.0, 10.0) -- 10.00 SELECT dbo.fnMaxValue (10.0, 12.0) -- 12.00 SELECT dbo.fnMaxValue (12.0, 10.0) -- 12.00 SELECT dbo.fnMaxValue (10.001, 10.002) -- 10.002 SELECT dbo.fnMaxValue (-12.0, -10.0) -- -10.00 GO -- Select the highest amount of tax and freight SELECT TOP ( 10 ) SalesOrderID, TaxAmt, Freight, Maximum = dbo.fnMaxValue(TaxAmt, Freight) FROM Sales.SalesOrderHeader ORDER BY NEWID() GO /* Results SalesOrderID TaxAmt Freight Maximum 74397 7.1976 2.2493 7.1976 51956 1.9024 0.5945 1.9024 44152 271.9992 84.9998 271.9992 67058 5.9984 1.8745 5.9984 69341 0.3992 0.1248 0.3992 61930 0.5824 0.182 0.5824 60116 138.8784 43.3995 138.8784 56853 5.4344 1.6983 5.4344 54184 43.1992 13.4998 43.1992 51704 2668.6276 833.9461 2668.6276
*/ ------------
|
 |
| The World Leader
in SQL Server 2008 Training |
| The future is just a CLICK away. Your future! |
| |
| SQLUSA.com
Home Page |
Copyright 2005-2010, SMI Corp. All Rights Reserved.
SQL Server 2008 is a program product of Microsoft Corporation. SQL Server 2005 is a program product of Microsoft Corporation. SQL Server 2000 is a program product of Microsoft Corporation. |
|