

/*=========================================================================================================
Procedure		: [usp_Coremark_OPBY_Migration]
Author			: Pavan K
Created date	: 23 Feb 2018
Description		: use this sp to include "TO" in OPBY while running migration tool
===========================================================================================================
	EXEC [usp_Coremark_SelectivityStacking_Migration] 2360
=========================================================================================================*/
CREATE PROCEDURE [dbo].[usp_Coremark_OPBY_Migration] 
			@FacilityIDs VARCHAR(MAX)
AS
BEGIN


	SELECT IDENTITY(INT,1,1) ID,CoreMarkID,LTRIM(RTRIM(SS.Value)) OPBY,CAST(NULL AS VARCHAR(20)) String,CAST(NULL AS INT) Number,CAST(NULL AS INT) IsSeq,CAST(NULL AS VARCHAR(MAX)) UpdatedOP
	INTO #CoreOP
	FROM CoreMark	CM
	CROSS APPLY dbo.udf_Table_SplitString(';',CM.OP_By) SS
	WHERE FacilityID IN (SELECT Value FROM dbo.udf_Table_SplitString(',',@FacilityIDs))
	AND		CM.OP_By IS NOT NULL
	ORDER BY CoreMarkID,dbo.udf_scalar_GetAlphanumericSortValue(SS.Value,1)


	UPDATE #CoreOP
	SET Number = REVERSE(LEFT(SUBSTRING(REVERSE(OPBY), PATINDEX('%[0-9]%', REVERSE(OPBY)), 8000),
			   PATINDEX('%[^0-9]%', SUBSTRING(REVERSE(OPBY), PATINDEX('%[0-9]%', REVERSE(OPBY)), 8000) + 'X') -1))
	WHERE ISNUMERIC(RIGHT(OPBY,1)) = 1

	UPDATE #CoreOP SET String = LEFT(OPBY,LEN(OPBY)-LEN(Number))

	UPDATE	A
	SET IsSeq = B.Seq
	FROM	#CoreOP A
	JOIN	(
				SELECT OB.*,DENSE_RANK()OVER(ORDER BY OB.CoreMarkID,OB.String) Seq
				FROM	#CoreOP	OB
				JOIN	#CoreOP SB ON OB.CoreMarkID = SB.CoreMarkID AND OB.ID+1 = SB.ID AND OB.String = SB.String AND OB.Number = SB.Number - 1
			)B ON A.ID = B.ID


	UPDATE	A
	SET IsSeq = B.Seq
	FROM	#CoreOP A
	JOIN	(
				SELECT SB.*,DENSE_RANK()OVER(ORDER BY SB.CoreMarkID,SB.String) Seq
				FROM	#CoreOP	OB
				JOIN	#CoreOP SB ON OB.CoreMarkID = SB.CoreMarkID AND OB.ID+1 = SB.ID AND OB.String = SB.String  AND OB.Number = SB.Number - 1
			)B ON A.ID = B.ID

	UPDATE #CoreOP SET UpdatedOP = OPBY WHERE IsSeq IS NULL


	UPDATE Old
	SET UpdatedOP = (SELECT MIN(I.String)+CAST(MIN(I.Number) AS VARCHAR(10))+' TO '+MIN(I.String)+CAST(MAX(I.Number) AS VARCHAR(10)) FROM #CoreOP I WHERE Old.CoreMarkID = I.CoreMarkID AND Old.IsSeq = I.IsSeq)
	FROM #CoreOP Old
	WHERE IsSeq IS NOT NULL

	--SELECT *,ROW_NUMBER()OVER(PARTITION BY CoremarkID,IsSeq ORDER BY ID) RNo FROM #CoreOP

	DELETE OP
	FROM #CoreOP OP
	JOIN (SELECT I.*,ROW_NUMBER()OVER(PARTITION BY I.CoremarkID,I.IsSeq ORDER BY I.ID) RNo FROM #CoreOP I WHERE I.IsSeq IS NOT NULL) B ON OP.ID = B.ID
	WHERE B.RNo > 1	AND B.IsSeq IS NOT NULL

	UPDATE	OCM
	SET		OCM.OP_By = TCM.OPBY
	FROM	Coremark	OCM
	JOIN	(
				SELECT	DISTINCT O.CoreMarkID,STUFF((SELECT '; '+I.UpdatedOP FROM #CoreOP I WHERE I.CoreMarkID = O.CoreMarkID FOR XML PATH('')),1,2,'') OPBY FROM #CoreOP O
			)	
			TCM ON OCM.CoremarkID = TCM.CoremarkID


	DROP TABLE #CoreOP

END;
