CREATE FUNCTION [dbo].[fn_GetSurfaceFieldAggregateAverage]
( 
	@surfaceItemId INT
	,@actionValue VARCHAR(255)
	,@skipAggregates BIT = 0
)
RETURNS INT
AS
BEGIN
DECLARE @counter INT
		,@returnVal INT

IF(@skipAggregates = 0)
BEGIN
	SELECT	@counter = COUNT(1)
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D
				ON D.surfaceFieldID = F.recId
	WHERE	D.surfaceItemId = @surfaceItemId
			AND F.recId IN (SELECT splitdata FROM SplitString(@actionValue,'|'))
			AND 0 < CASE F.surfaceFieldTypeId 
						WHEN 4
						THEN D.surfaceFieldValueNum
						WHEN 5
						THEN D.surfaceFieldValueDecimal
						WHEN 14 THEN
							CASE WHEN F.actionType = 8
							THEN CASE F.[action]
								WHEN 12
									THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(@surfaceItemId,F.actionValue,1) AS VARCHAR(MAX))
								WHEN 14
									THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(@surfaceItemId,F.actionValue) AS VARCHAR(MAX))
								WHEN 15
									THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(@surfaceItemId,F.actionValue) AS VARCHAR(MAX))
								END
							END					
						END

	SELECT	@returnVal = ROUND(SUM(CASE F.surfaceFieldTypeId 
				WHEN 4
				THEN D.surfaceFieldValueNum
				WHEN 5
				THEN D.surfaceFieldValueDecimal
				WHEN 14 THEN
					CASE WHEN F.actionType = 8
					THEN CASE F.[action]
						WHEN 12
							THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(@surfaceItemId,F.actionValue,1) AS VARCHAR(MAX))
						WHEN 14
							THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(@surfaceItemId,F.actionValue) AS VARCHAR(MAX))
						WHEN 15
							THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(@surfaceItemId,F.actionValue) AS VARCHAR(MAX))
						END
					END					
				END)/@counter,0)
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D
				ON D.surfaceFieldID = F.recId
	WHERE	D.surfaceItemId = @surfaceItemId
			AND F.recId IN (SELECT splitdata FROM SplitString(@actionValue,'|'))
			AND 0 < CASE F.surfaceFieldTypeId 
						WHEN 4
						THEN D.surfaceFieldValueNum
						WHEN 5
						THEN D.surfaceFieldValueDecimal
						WHEN 14 THEN
							CASE WHEN F.actionType = 8
							THEN CASE F.[action]
								WHEN 12
									THEN CAST(dbo.fn_GetSurfaceFieldAggregateAverage(@surfaceItemId,F.actionValue,1) AS VARCHAR(MAX))
								WHEN 14
									THEN CAST(dbo.fn_GetSurfaceFieldAggregateDistinctionCount(@surfaceItemId,F.actionValue) AS VARCHAR(MAX))
								WHEN 15
									THEN CAST(dbo.fn_GetSurfaceFieldAggregateSum(@surfaceItemId,F.actionValue) AS VARCHAR(MAX))
								END
							END
						END

END
ELSE
BEGIN
	SELECT	@counter = COUNT(1)
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D
				ON D.surfaceFieldID = F.recId
	WHERE	D.surfaceItemId = @surfaceItemId
			AND F.recId IN (SELECT splitdata FROM SplitString(@actionValue,'|'))
			AND 0 < CASE F.surfaceFieldTypeId 
						WHEN 4
						THEN D.surfaceFieldValueNum
						WHEN 5
						THEN D.surfaceFieldValueDecimal
						END

	SELECT	@returnVal = ROUND(SUM(CASE F.surfaceFieldTypeId 
				WHEN 4
				THEN D.surfaceFieldValueNum
				WHEN 5
				THEN D.surfaceFieldValueDecimal
				END)/@counter,0)
	FROM	pal_SurfaceField F
			JOIN pal_SurfaceFieldData D
				ON D.surfaceFieldID = F.recId
	WHERE	D.surfaceItemId = @surfaceItemId
			AND F.recId IN (SELECT splitdata FROM SplitString(@actionValue,'|'))
			AND 0 < CASE F.surfaceFieldTypeId 
						WHEN 4
						THEN D.surfaceFieldValueNum
						WHEN 5
						THEN D.surfaceFieldValueDecimal
						END
END
	RETURN	@returnVal
END