-- =============================================
-- Author:		Jas Rademeyer
-- Create date: 2017-06-10
-- Description:	Custom for SBF: Calculates the Tertiary Aggregate
-- =============================================
-- Modified: CVH 2017-11-02 Calculate as SUM courses with marks - (Number of courses taken - Number of courses without marks)
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_CalculateTertiaryAggregate] 
	@surfaceId INT, 
	@surfaceItemId INT
AS
BEGIN
	SELECT CASE WHEN ISNULL(D1.surfaceFieldValueNum,0) - ISNULL(D2.surfaceFieldValueNum,0) > 0 
	THEN dbo.fn_GetSurfaceFieldAggregateSUM(I.recId,F3.actionValue) / (ISNULL(D1.surfaceFieldValueNum,0) - ISNULL(D2.surfaceFieldValueNum,0))
	ELSE 0
	END
	FROM	pal_Surface S
			JOIN pal_SurfaceItem I
				ON I.surfaceId = S.recId
			LEFT JOIN pal_SurfaceField F1
				ON F1.surfaceFieldDisplay = 'Number of Courses Taken'
				AND F1.surfaceId = S.recId
			LEFT JOIN pal_SurfaceFieldData D1
				ON D1.surfaceFieldID = F1.recId
				AND D1.surfaceId = S.recId
				AND D1.surfaceItemId = I.recId
			LEFT JOIN pal_SurfaceField F2
				ON F2.surfaceFieldDisplay = 'Number of Courses Without Marks'
				AND F2.surfaceId = S.recId
			LEFT JOIN pal_SurfaceFieldData D2
				ON D2.surfaceFieldID = F2.recId
				AND D2.surfaceId = S.recId
				AND D2.surfaceItemId = I.recId
			LEFT JOIN pal_SurfaceField F3
				ON F3.surfaceFieldDisplay = 'Sum of Marks'
				AND F3.surfaceId = S.recId
	WHERE	S.recId = @surfaceId 
	AND		I.recId = @surfaceItemId

END