Find Median For Given Set Of Numbers

/*

   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