CREATE FUNCTION [dbo].[fn_STUD_1_GetPrepMathsAveragePrecentage](
	@name VARCHAR(50)
	,@surname VARCHAR(50)
	,@year VARCHAR(4)
)
RETURNS DECIMAL(5,2)
AS
BEGIN

RETURN
(SELECT	CASE WHEN (SUM(CASE WHEN Prep2017_SessionReviewTool_MathsResult = '' OR Prep2017_SessionReviewTool_MathsResult = 0 THEN 0 ELSE 1 END)) = 0
			THEN 0.0
			ELSE
				(SUM(CAST(CASE WHEN Prep2017_SessionReviewTool_MathsResult = '' OR Prep2017_SessionReviewTool_MathsResult = 0 THEN 0 ELSE Prep2017_SessionReviewTool_MathsResult END AS DECIMAL(5,2))))
				/(CAST(SUM(CASE WHEN Prep2017_SessionReviewTool_MathsResult = '' OR Prep2017_SessionReviewTool_MathsResult = 0 THEN 0 ELSE 1 END)AS DECIMAL(5,2)))
			END
FROM	dbo.qt_PrepProgramme2017 
WHERE	Prep2017_SessionReviewTool_Name = @name
		AND Prep2017_SessionReviewTool_Surname = @surname
		AND Prep2017_SessionReviewTool_PrepProgrammeYear = @year
		AND Prep2017_SessionReviewTool_Date <= GETDATE()
)
END



GO


