-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-07-11
-- Description:	Update Query Table Rank Data
-- =============================================
CREATE PROCEDURE sp_UpdateQueryTableRankData
	@surfaceId INT,
	@rankFieldName VARCHAR(150), 
	@rankFieldSource INT, 
	@rankFieldGroupBy VARCHAR(250)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    DECLARE @QueryTableName varchar(300) = ''
	SET @QueryTableName = 'qt_' + (SELECT name from pal_Surface Where recId = @surfaceId)

	DECLARE @rankFieldSourceName VARCHAR(150)
	SELECT @rankFieldSourceName = surfaceFieldName FROM pal_SurfaceField WHERE recId = @rankFieldSource

	DECLARE @query VARCHAR(MAX)

	SET @query = 'UPDATE up 
		SET up.' + @rankFieldName + ' = rst.RankValue 
		FROM ' + @QueryTableName + ' up
		INNER JOIN (SELECT itemID, 
			CASE WHEN ISNULL(' + @rankFieldSourceName + ', '''') = '''' THEN '''' 
			ELSE 
				CAST(DENSE_RANK() OVER
					(PARTITION BY
						(
						SELECT STUFF((SELECT '','' + D2.surfaceFieldValueChar
						FROM pal_SurfaceField F2  
						JOIN pal_SurfaceFieldData D2 ON D2.surfaceFieldID = F2.recId AND D2.surfaceItemId = qt.itemID
						WHERE F2.surfaceId = qt.surfaceId 
						AND F2.recId IN (SELECT CASE WHEN ISNUMERIC(splitdata) = 1 THEN splitdata ELSE 0 END FROM dbo.SplitString(''' + @rankFieldGroupBy + ''', ''|''))
						FOR XML PATH(''''), TYPE
								).value(''.'', ''VARCHAR(MAX)'') 
								,1,1,'''')
						)    
					ORDER BY ( ' + @rankFieldSourceName + ' )  
					DESC)
					AS VARCHAR(MAX))
			END AS RankValue
			FROM ' + @QueryTableName + ' qt 
		) rst ON rst.itemID = up.itemID   '

	--print @query
	exec (@query)


END
GO
