-- =============================================
-- Author:		Jas Rademeyer
-- Create date: 2017-06-10
-- Description:	Custom for SBF: Calculates the Tertiary Aggregate
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_CalculateTertiaryAggregate] 
	@surfaceId INT, 
	@surfaceItemId INT
AS
BEGIN
	SELECT	CASE WHEN ISNULL(D.surfaceFieldValueNum,0) > 0
			THEN dbo.fn_GetSurfaceFieldAggregateSUM(I.recId,F2.actionValue)/D.surfaceFieldValueNum
			ELSE 0
			END
	FROM	pal_Surface S
			JOIN pal_SurfaceItem I
				ON I.surfaceId = S.recId
			LEFT JOIN pal_SurfaceField F
				ON F.surfaceFieldDisplay = 'Number of Courses Taken'
				AND F.surfaceId = S.recId
			LEFT JOIN pal_SurfaceFieldData D
				ON D.surfaceFieldID = F.recId
				AND D.surfaceId = S.recId
				AND D.surfaceItemId = I.recId
			LEFT JOIN pal_SurfaceField F2
				ON F2.surfaceFieldDisplay = 'Sum of Marks'
				AND F2.surfaceId = S.recId
	WHERE	S.recId = @surfaceId
			AND I.recId = @surfaceItemId

END