--Holding table for quartiles rows
CREATE TABLE #ExamStats (sub_code VARCHAR(4), Q0 decimal(5,2), Q1 decimal(5,2), Q2 decimal(5,2), Q3 decimal(5,2), Q4 decimal(5,2), mean decimal(5,2), NumRows int)
CREATE TABLE #ExamResults (
    ID int IDENTITY(1,1)    
    , result decimal(5,2)
)

DECLARE @subjcode varchar(4), @q2 decimal(5,2)
    , @mid decimal(5,2), @odd int, @mid1 decimal(5,2), @mid2 decimal(5,2)    
    , @NumRows  int
    
DECLARE @q1max int, @q1 decimal(5,2), @q3 decimal(5,2), @q3min int
DECLARE @q0 decimal(5,2), @q4 decimal(5,2), @mean decimal(5,2)
    
DECLARE oCursor CURSOR FOR
SELECT distinct sub_code
FROM         studresult
WHERE     (cmpy_code = '01') AND (res_year = '2011') AND (res_period = '3') and (obj_code like 'SE%%') and ISNUMERIC(stud_result)= 1  --AND (sub_code = '8041') 
ORDER BY sub_code asc
    
--Open said cursor and start stepping thru it
OPEN oCursor
WHILE 0=0 BEGIN
    FETCH NEXT FROM oCursor INTO @subjcode    
    IF @@Fetch_Status<>0 BREAK    
    TRUNCATE TABLE #ExamResults
     
	--Insert the data for a given age    
	INSERT INTO #ExamResults (result)    
	SELECT     stud_result
	FROM         studresult
	WHERE     (cmpy_code = '01') AND (res_year = '2011') AND (res_period = '3') and (obj_code like 'SE%%') and ISNUMERIC(stud_result)= 1 AND (sub_code = @subjcode) 
	ORDER BY stud_result asc
	    
	--Now actually compute the median    
	SET @NumRows  = @@ROWCOUNT        
	SET @odd = @NumRows  % 2        

	--1 if odd number of recs, 0 if even    
	SET @mid = @NumRows  / 2        

	--Number of the "middle" record    
	IF @NumRows  = 1 BEGIN            --Special trick so this works if only one record        
		SET @odd = 1        
		SET @mid = 1    
	END    -- Which row(s) hold the median value?    

	IF @odd = 1 BEGIN    
		--Odd number of rows - median is the middle one        
		SET @q2 = (SELECT result FROM #ExamResults WHERE ID = @mid)  
		
		--To Reset Midpoints
		SET @q1max = @mid - 1
		SET @q3min = @mid + 1
	END ELSE BEGIN    
		--Even number of rows - median is the average of the two middle ones        
		SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid)        
		SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1)       
		SET @q2 = (@mid1 + @mid2) / 2
		
		--To Reset Midpoints
		SET @q1max = @mid
		SET @q3min = @mid + 1
	END

	--Q1
	SET @odd = @q1max % 2
	SET @mid = @q1max / 2
	if @q1max = 1 BEGIN
		SET @odd = 1
		SET @mid = 1
	END
	IF @odd = 1 BEGIN
		SET @q1 = (SELECT result FROM #ExamResults WHERE ID = @mid)
	END ELSE BEGIN    
		SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid)        
		SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1)       
		SET @q1 = (@mid1 + @mid2) / 2
	END
	--Q3        
	SET @odd = (@NumRows-@q3min) % 2
	SET @mid = ((@NumRows-@q3min) / 2) + @q3min
	if (@NumRows-@q3min) = 1 BEGIN
		SET @odd = 1
		SET @mid = 1
	END
	IF @odd = 1 BEGIN
		SET @q3 = (SELECT result FROM #ExamResults WHERE ID = @mid)
	END ELSE BEGIN    
		SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid)        
		SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1)       
		SET @q3 = (@mid1 + @mid2) / 2
	END
	
	SET @q0 = (SELECT MIN(result) FROM #ExamResults)
	SET @q4 = (SELECT MAX(result) FROM #ExamResults)	
	SET @mean = (SELECT AVG(result) FROM #ExamResults)

	INSERT into #ExamStats values(@subjcode, @q0, @q1, @q2, @q3, @q4, @mean, @NumRows)
END
CLOSE oCursor 
DEALLOCATE oCursor

SELECT     student.stud_code, student.surname, student.given_name, studresult.res_year, studresult.res_period,
                      student.year_grp, student.house, student.form_cls, student.pctut_grp, subtab.sub_long,
                      studresult.sub_code, studresult.stud_result, #ExamStats.Q0, #ExamStats.Q1, #ExamStats.Q2, #ExamStats.Q3, #ExamStats.Q4, #ExamStats.mean, #ExamStats.NumRows
FROM         student INNER JOIN
                      studresult ON student.stud_code = studresult.stud_code AND student.cmpy_code = studresult.cmpy_code INNER JOIN
                      #ExamStats ON studresult.sub_code = #ExamStats.sub_code INNER JOIN
                      subtab on subtab.cmpy_code = student.cmpy_code and subtab.sub_code = studresult.sub_code
WHERE      studresult.res_year = '2011' and  studresult.res_period = 3 and (obj_code like 'SE%%') and student.cmpy_code = '01'   
ORDER BY   studresult.res_year, studresult.res_period, studresult.sub_code, student.stud_code      
                      
DROP TABLE #ExamStats
DROP TABLE #ExamResults