MEDIAN RULE: IF COUNT OF
NUMBERS IS ODD THEN THE VALUE IS MIDDLE NUMBER: FOR EXAMPLE MEDIAN FOR 1,8,10
WILL BE 8
IF COUNT OF NUMBER
IS EVEN THEN THE VALUE IS SUM(MIDDLE NUMBER + SUCCEEDING NUMBER TO MIDDLE
NUMBER)/2.0 ESSENTIALLY ITS AN AVERAGE: FOR EXAMPLE 1,2,4,8
MEDIAN IS
(2+4)/2.0 WHICH IS 3.
*/
DECLARE @ArrayForMedian VARCHAR(MAX) = '1,2,100,23,11,16,17,21,19,33,21,24'
DECLARE @FirstMedianValue AS FLOAT
DECLARE @SecondMedianValue AS FLOAT
DECLARE @MedianValue AS FLOAT
DECLARE @MedianTable AS TABLE
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,Value INT NOT NULL
)
--traverse and split the string into individual numbers
;WITH ValueCTE
AS
(
SELECT SUBSTRING(@ArrayForMedian + ',', 1, CHARINDEX(',',
@ArrayForMedian +',') - 1) AS val
,SUBSTRING(@ArrayForMedian + ',',CHARINDEX(',',
@ArrayForMedian +',') + 1, LEN(@ArrayForMedian + ',')) AS Rem
UNION ALL
SELECT SUBSTRING(Rem, 1, CHARINDEX(',',Rem) -1)
,SUBSTRING(Rem, CHARINDEX(',', Rem) + 1, LEN(Rem))
FROM ValueCTE
WHERE CHARINDEX(',', Rem) <> 0
)
INSERT INTO @MedianTable
(
Value
)
SELECT Val
FROM ValueCTE
--EVEN COUNT OF NUMBERS
IF (SELECT COUNT(*)%2
FROM @MedianTable) = 0
BEGIN
SELECT @FirstMedianValue = Value
FROM @MedianTable
WHERE ID = (SELECT COUNT(*)/2
FROM @MedianTable)
SELECT @SecondMedianValue = Value
FROM @MedianTable
WHERE ID = (SELECT (COUNT(*)/2) + 1
FROM @MedianTable)
SELECT @MedianValue = (@FirstMedianValue + @SecondMedianValue)/2.0
END
ELSE
--ODD COUNT OF NUMBERS
BEGIN
SELECT @MedianValue = Value
FROM @MedianTable
WHERE ID = (SELECT CEILING(COUNT(*)/2.0)
FROM @MedianTable)
END
SELECT @MedianValue
