Anal Patel alternate quartile stored proc

--From comment by Anal Patel at http://electrovoid.wordpress.com/2011/08/15/quartile-sql-server/
--** Procedure to Find Median,1st Quartile,3rd Quartile,Minimum and Maximum
ALTER procedure dbo.sp_calc_median
(@tablename varchar(50),
@columnname varchar(50),
@result sql_variant OUTPUT)
as
BEGIN
declare @sqlstmt varchar(200);
declare @stmt nvarchar(max);
DECLARE @rCount INT;
DECLARE @mPos1 INT;
DECLARE @mPos2 INT;
DECLARE @Q1 INT;
DECLARE @Q2 INT;
DECLARE @Q3 INT;
DECLARE @Q4 INT;
DECLARE @result1 SQL_VARIANT;
DECLARE @result2 sql_variant;
DECLARE @resultQ1 sql_variant;
DECLARE @resultQ2 sql_variant;
DECLARE @resultQ3 sql_variant;
DECLARE @resultQ4 sql_variant;
DECLARE @Minimum DECIMAL(10,2);
DECLARE @Maximum DECIMAL(10,2);

IF object_id('temp_incentive') IS NULL
BEGIN
CREATE TABLE temp_incentive
(
type VARCHAR(10),
COMPUTER DECIMAL(10,2),
LAPTOP DECIMAL(10,2),
MOUSE DECIMAL(10,2)
)

INSERT INTO temp_incentive (type) VALUES ('Median')
INSERT INTO temp_incentive (type) VALUES ('FirstQ')
INSERT INTO temp_incentive (type) VALUES ('ThirdQ')
INSERT INTO temp_incentive (type) VALUES ('Minimum')
INSERT INTO temp_incentive (type) VALUES ('Maximum')
END

set nocount ON
set @sqlstmt = 'insert #tempmedian select ' + @columnname +
' from ' + @tablename + ' order by 1 asc '

create table #tempmedian (col sql_variant)
exec (@sqlstmt)

SELECT @rCount= count(*) FROM #tempmedian
SELECT @Minimum = Min(convert(DECIMAL(10,2),col)) FROM #tempmedian
SELECT @Maximum = max(convert(DECIMAL(10,2),col)) FROM #tempmedian

--UPDATE temp_incentive SET @columnname = @Minimum WHERE type = 'Minimum'

SET @stmt = 'update temp_incentive SET '+@columnname+' = '+convert(VARCHAR(max),@Minimum)+' where type = ''Minimum''' ;
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET '+@columnname+' = '+convert(VARCHAR(max),@Maximum)+' where type = ''Maximum''' ;
EXEC sp_executesql @stmt

--median
--***************************************************************************
IF (@rCount%2 = 0)
BEGIN
SET @mPos1 = (@rCount/2)
SET @mPos2 = @mPos1+1

-- if @mpos1 & @mpos2 is even

IF(@mPos1%2 =0)
BEGIN

SET @Q1 = (@mPos1/2)
SET @Q2 = @Q1+1

SET @Q3 = (@mPos2+@rCount)/2
SET @Q4 = @Q3+1

-- PRINT '@rCount'
-- PRINT @rCount
-- PRINT '@mPos1'
-- PRINT @mPos1
-- PRINT '@mPos2'
-- PRINT @mPos2
-- PRINT '@Q1'
-- PRINT @Q1
-- PRINT '@Q2'
-- PRINT @Q2
-- PRINT '@Q3'
-- PRINT @Q3
-- PRINT '@Q4'
-- PRINT @Q4

declare c_med cursor scroll for select * from #tempmedian
open c_med
fetch absolute @mPos1 from c_med into @result1
fetch absolute @mPos2 from c_med into @result2
fetch absolute @Q1 from c_med into @resultQ1
fetch absolute @Q2 from c_med into @resultQ2
fetch absolute @Q3 from c_med into @resultQ3
fetch absolute @Q4 from c_med into @resultQ4
close c_med
deallocate c_med

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;
-- PRINT @stmt
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2.0)+' where type = ''FirstQ''' ;
-- PRINT @stmt
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2.0)+' where type = ''ThirdQ''' ;
-- PRINT @stmt
EXEC sp_executesql @stmt

-- SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)
-- SELECT ((convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2)
-- SELECT ((convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2)
END

-- if @mpos1 & @mpos2 is odd

ELSE
BEGIN

SET @Q1 = (@mPos1+1)/2
SET @Q2 = @Q1+(@rCount/2)

-- PRINT @mPos1
-- PRINT @mPos2
-- PRINT @Q1
-- PRINT @Q2

declare c_med cursor scroll for select * from #tempmedian
open c_med
fetch absolute @mPos1 from c_med into @result1
fetch absolute @mPos2 from c_med into @result2
fetch absolute @Q1 from c_med into @resultQ1
fetch absolute @Q2 from c_med into @resultQ2
close c_med
deallocate c_med

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ1))+' where type = ''FirstQ''' ;
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ2))+' where type = ''ThirdQ''' ;
EXEC sp_executesql @stmt

-- SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)
-- SELECT ((convert(DECIMAL(10,2),@resultQ1)))
-- SELECT ((convert(DECIMAL(10,2),@resultQ2)))
END

END

--***************************************************************************************
--***************************************************************************************
-- Median when count is odd

ELSE
BEGIN

SET @mPos1 = (@rCount+1)/2
SET @mPos2 = @mPos1+1
-- if @mpos1 & @mpos2 is odd

IF(@mPos1%2 = 1)
BEGIN

SET @Q1 = (@mPos1/2)
SET @Q2 = @Q1+1

SET @Q3 = (@mPos2+@rCount)/2
SET @Q4 = @Q3+1

-- PRINT '@rCount'
-- PRINT @rCount
-- PRINT '@mPos1'
-- PRINT @mPos1
-- PRINT '@Q1'
-- PRINT @Q1
-- PRINT '@Q2'
-- PRINT @Q2
-- PRINT '@Q3'
-- PRINT @Q3
-- PRINT '@Q4'
-- PRINT @Q4

declare c_med cursor scroll for select * from #tempmedian
open c_med
fetch absolute @mPos1 from c_med into @result1
fetch absolute @Q1 from c_med into @resultQ1
fetch absolute @Q2 from c_med into @resultQ2
fetch absolute @Q3 from c_med into @resultQ3
fetch absolute @Q4 from c_med into @resultQ4
close c_med
deallocate c_med

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@result1))+' where type = ''Median''' ;
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2.0)+' where type = ''FirstQ''' ;
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2.0)+' where type = ''ThirdQ''' ;
EXEC sp_executesql @stmt

-- SELECT ((convert(DECIMAL(10,2),@result1)))
-- SELECT ((convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2)
-- SELECT ((convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2)

END

-- if @mpos1 & @mpos2 is even

ELSE
BEGIN

SET @Q1 = (@mPos1+1)/2
SET @Q2 = (@Q1+(@rCount/2))+1

-- PRINT @mPos1
-- PRINT @mPos2
-- PRINT @Q1
-- PRINT @Q2

declare c_med cursor scroll for select * from #tempmedian
open c_med
fetch absolute @mPos1 from c_med into @result1
fetch absolute @mPos2 from c_med into @result2
fetch absolute @Q1 from c_med into @resultQ1
fetch absolute @Q2 from c_med into @resultQ2
close c_med
deallocate c_med

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ1))+' where type = ''FirstQ''' ;
EXEC sp_executesql @stmt

SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ2))+' where type = ''ThirdQ''' ;
EXEC sp_executesql @stmt

-- SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)
-- SELECT ((convert(DECIMAL(10,2),@resultQ1)))
-- SELECT ((convert(DECIMAL(10,2),@resultQ2)))
END
END

--********************************************************

END
GO

--*********************** Test Data ***********************

IF OBJECT_ID ('dbo.TEST_MEDIAN') IS NOT NULL
DROP TABLE dbo.TEST_MEDIAN
GO

CREATE TABLE dbo.TEST_MEDIAN
(
NAME VARCHAR (50) NOT NULL,
COMPUTER INT NOT NULL,
LAPTOP INT NOT NULL,
MOUSE INT NOT NULL,
ID INT IDENTITY NOT NULL
)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Document Control', 150, 78, 65)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Engineering', 100, 89, 26)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Executive', 75, 45, 75)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Facilities and Maintenance', 90, 65, 45)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Finance', 45, 32, 43)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Human Resources', 62, 25, 73)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Information Services', 85, 68, 91)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Marketing', 95, 98, 82)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Production', 45, 52, 93)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Production Control', 32, 56, 95)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Purchasing', 85, 45, 46)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Quality Assurance', 63, 75, 49)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Research and Development', 84, 53, 76)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Sales', 75, 32, 61)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Shipping and Receiving', 12, 62, 65)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Tool Design', 96, 85, 67)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Control', 200, 178, 165)
GO

-- ********************* Execute SP ***********************

DECLARE @result sql_variant
EXEC dbo.sp_calc_median 'dbo.test_median', 'COMPUTER', @result OUTPUT

DECLARE @result1 sql_variant
EXEC dbo.sp_calc_median 'dbo.test_median', 'LAPTOP', @result1 OUTPUT

DECLARE @result2 sql_variant
EXEC dbo.sp_calc_median 'dbo.test_median', 'MOUSE', @result2 OUTPUT

SELECT * FROM temp_incentive

DROP TABLE temp_incentive