-- =============================================
-- Author:		Jas Rademeyer
-- Create date: 2017-06-10
-- Description:	Custom for SBF: Calculates the % Tertiary Subjects Failed
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_CalculatePercentageTertiarySubjectsFailed] 
	@surfaceId INT, 
	@surfaceItemId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT	CASE WHEN ISNULL(dNoCourses.surfaceFieldValueNum,0) > 0
			THEN (CAST(dNoFailed.surfaceFieldValueNum AS DECIMAL(18,2))/dNoCourses.surfaceFieldValueNum)*100
			ELSE 0
			END
	FROM	pal_Surface S
			JOIN pal_SurfaceItem I
				ON I.surfaceId = S.recId
			LEFT JOIN pal_SurfaceField fNoCourses
				ON fNoCourses.surfaceFieldDisplay = 'Number of Courses Taken'
				AND fNoCourses.surfaceId = S.recId
			LEFT JOIN pal_SurfaceFieldData dNoCourses
				ON dNoCourses.surfaceFieldID = fNoCourses.recId
				AND dNoCourses.surfaceId = S.recId
				AND dNoCourses.surfaceItemId = I.recId
			LEFT JOIN pal_SurfaceField fNoFailed
				ON fNoFailed.surfaceFieldDisplay = 'Number of Subjects Failed'
				AND fNoFailed.surfaceId = S.recId
			LEFT JOIN pal_SurfaceFieldData dNoFailed
				ON dNoFailed.surfaceFieldID = fNoFailed.recId
				AND dNoFailed.surfaceId = S.recId
				AND dNoFailed.surfaceItemId = I.recId
	WHERE	S.recId = @surfaceId
			AND I.recId = @surfaceItemId
END

