-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2018-08-08
-- Description:	Custom GetSurfaceAggrSum proc for Jewel Africa. Taking too long to execute. 
-- Assumptions: No linked surfaces, all surfaces are child surfaces, and no surface is linked on a lower level (like SBF). Just summing current level.
-- =============================================
CREATE PROCEDURE [dbo].[sp_JEWE1_GetSurfaceAggrSum] 
	@surfaceId INT,
	@surfaceFieldId INT,
	@parentSurfaceItemId INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @qtTable as VARCHAR(500), @fieldName as VARCHAR(150)
	SET @qtTable = 'qt_' + (SELECT name from pal_Surface Where recId = @surfaceId)
	SELECT @fieldName = surfaceFieldName FROM pal_SurfaceField WHERE recId = @surfaceFieldId

	DECLARE @query AS VARCHAR(MAX)
	SET @query = N'select ISNULL(SUM(CASE WHEN ' + @fieldName + ' = '''' THEN 0 ELSE CAST(' + @fieldName + ' AS DECIMAL(18,2)) END), 0)
	from ' + @qtTable + ' 
	where ParentSurfaceItemId = ' + CAST( @parentSurfaceItemId AS VARCHAR(50)) + ' '

	exec (@query)

END
