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

RETURN
(SELECT	ROUND(CAST((100 * SUM(CAST(CASE WHEN Prep2017_SessionReviewTool_Attendance IN ('A','L') THEN 1 ELSE 0 END AS DECIMAL(4,2))))
		/(CAST(COUNT(Prep2017_SessionReviewTool_Date)AS DECIMAL(4,2))) AS DECIMAL(4,2)),2)
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


