Minimum of two values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Minimum of two values

Author

Topic

rsmohankumar
Starting Member

11 Posts

Posted - 2015-04-13 : 05:20:15

Hi,

I want the minimum of two values to be display like below without using the case

MIN(3.00,4.00)

output:

3.00

Can anyone let me is there any function available to do it.

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-13 : 06:19:47

Without using CASE?

If you are in version 2012, you could use

IIF(col1<col2,col1,col2)

Madhivanan

Failing to plan is Planning to fail

Maithil
Starting Member

29 Posts

Posted - 2015-04-13 : 07:33:58

Hi
You need to 2 functions
One Function will separates your all values
and another will find the Minimum Value among it...

Create below two Functions.
-----------------------------------------------

CREATE FUNCTION [dbo].[NvarcharListToTable] (@list NVARCHAR(MAX), @seperator NVARCHAR(10) = ',')
RETURNS @tbl TABLE (number NVARCHAR(MAX))
AS
BEGIN
DECLARE @position int
DECLARE @NewLine char(2)
DECLARE @no NVARCHAR(MAX)
declare @startIndex int
set @NewLine = char(13)+char(10)

SET @position = 1
SET @list = @list + @seperator
set @startIndex = charindex(@seperator,@list,@position)

WHILE @startIndex <> 0
BEGIN
SELECT @no = substring(@list, @position, @startIndex - @position)
IF @no <> ''
INSERT into @tbl values(@no)
SET @position = @startIndex + len(replace(@seperator,' ','C'))
set @startIndex = charindex(@seperator,@list,@position)
END
update @tbl set number = Replace(number , @NewLine,'') WHERE number like '%' + @NewLine +'%'
RETURN
END

GO
-----------------------------------------------
CREATE FUNCTION GetMinValue
(
@Val nvarchar(max)
)
RETURNS float
AS
BEGIN
DECLARE @Result float
DECLARE @Table TAble(Value float)

INSERT INTO @Table
select number from dbo.NvarcharListToTable(@val,',')

SET @Result=(select Min(Value) from @table)

RETURN @result

END
GO
-----------------------------------------------

After Creation above two Functions

Run
select dbo.GetMinValue( '13.0,4.0,5.0,1.0,2.0')
-----------------------------------------------

Hope you will get what you want exactly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources