MSSQL MAX, MIN from the column data

How to compare and find the MAX, MIN from the column data?
Some time we want to find the MAX, MIN form our own data or Colunm data On that case MSSQL dose not have system function to do it but here is the solution to do it.

See the example to find Max value:

-- select dbo.FuncGetMax(123,5,null,null,null)


ALTER FUNCTION [dbo].[FuncGetMax] (@str float, @str1 float, @str2 float, @str3 float,@str4 float)
RETURNS float
BEGIN

Declare @result int

-- First compare
if @str > @str1
set @result=@str
else
set @result=@str1

-- 2nd compare
if @str2 is null OR @result > @str2
set @result=@result
else
set @result=@str2

-- 3rd compare
if @str3 is null OR @result > @str3
set @result=@result
else
set @result=@str3

-- 4th compare
if @str4 is null OR @result > @str4
set @result=@result
else
set @result=@str4

return @result

end

See the example to find MIN value:


-- select dbo.FuncGetMIN(123,5,null,null,null)

Alter FUNCTION [dbo].FuncGetMIN(@str float, @str1 float, @str2 float, @str3 float,@str4 float)
RETURNS float
BEGIN

Declare @result int

-- First compare
if @str < @str1
set @result=@str
else
set @result=@str1

-- 2nd compare
if @str2 is null OR @result < @str2
set @result=@result
else
set @result=@str2


-- 3rd compare

if @str3 is null OR @result < @str3
set @result=@result
else
set @result=@str3

-- 4th compare
if @str4 is null OR @result < @str4
set @result=@result
else
set @result=@str4


return @result

end

MSSQL Cross-Tab Query

Example Script for MSSQL Cross-Tab Query

Create table #Table
(
yearofJoining int,
EmpId int,
Deptid int
)
GO


insert into #Table select 1990,1,1
insert into #Table select 1991,2,2
insert into #Table select 1990,3,4
insert into #Table select 1991,4,2
insert into #Table select 1990,5,1
insert into #Table select 1990,6,3
insert into #Table select 1992,7,3
insert into #Table select 1990,8,4
insert into #Table select 1993,9,1
insert into #Table select 1994,10,2
insert into #Table select 1990,11,3
insert into #Table select 1995,12,3
insert into #Table select 1995,14,3
insert into #Table select 1995,15,3
insert into #Table select 1995,16,6
GO

--Original Cross Tab query
select YearofJoining,
count(case [DeptId] when 1 then 1 else null end) as [Department-1],
count(case [DeptId] when 2 then 1 else null end) as [Department-2],
count(case [DeptId] when 3 then 1 else null end) as [Department-3]
from #Table where deptid in(1,2,3)
group by Yearofjoining

Out put:

YearofJoining Department-1 Department-2 Department-3
------------- ------------ ------------ ------------
1990 2 0 2
1991 0 2 0
1992 0 0 1
1993 1 0 0
1994 0 1 0
1995 0 0 3

PIVOT Example:


SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #Table) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining