-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 3c
--
-- Remove duplicates from DataLog2.
-- ------------------------------------------------------------------------------------

SELECT dbo.FormatStatusMessage('Step 3c: Started.')

DECLARE @SourceIDToProcess INT
DECLARE @QuantityIDToProcess SMALLINT
DECLARE @RowIDToProcess INT
DECLARE @PairsToProcess TABLE
(
	Ranking INT PRIMARY KEY IDENTITY(1,1),
	SourceID INT,
	QuantityID SMALLINT
)

SET NOCOUNT ON 

INSERT INTO
	@PairsToProcess(SourceID, QuantityID)
SELECT DISTINCT
	d.SourceID,
	d.QuantityID
FROM
	dbo.Datalog2 d
ORDER BY
	d.SourceID,
	d.QuantityID
	
DECLARE @PairsRemaining INT
SELECT @PairsRemaining = COUNT(*) FROM @PairsToProcess

INSERT INTO dbo.tmpRemoveDuplicatesProgress (PairsToProcess) VALUES (@PairsRemaining)

DECLARE @StartTime DATETIME
SET @StartTime = GETUTCDATE()
DECLARE @PairsCount INT
SET @PairsCount = @PairsRemaining

WHILE EXISTS (SELECT 1 FROM @PairsToProcess)
BEGIN

	SELECT TOP 1
		@SourceIDToProcess = SourceID,
		@QuantityIDToProcess = QuantityID,
		@RowIDToProcess = Ranking
	FROM
		@PairsToProcess
	ORDER BY
		Ranking

	DECLARE @ExecutionSeconds INT
	SET @ExecutionSeconds = DATEDIFF(ss, @StartTime, GETUTCDATE())+1
	DECLARE @PairsPerSecond FLOAT
	SET @PairsPerSecond = CONVERT(float,(@PairsCount+1-@PairsRemaining))/CONVERT(float,@ExecutionSeconds)
	DECLARE @SecondsRemaining INT
	SET @SecondsRemaining = @PairsRemaining/@PairsPerSecond
	DECLARE @EstimatedCompletion DATETIME
	SET @EstimatedCompletion = DATEADD(ss, @SecondsRemaining, GETDATE())


	PRINT
		'Estimated completion: ' + CONVERT(NVARCHAR,@EstimatedCompletion,120) + '. '
		+ 'Removing duplicates from SourceID #' + CONVERT(NVARCHAR,@SourceIDToProcess)
		+ '/QuantityID #' + CONVERT(NVARCHAR,@QuantityIDToProcess)
		+ '. '
		+ CONVERT(NVARCHAR,@PairsRemaining) + ' pair(s) left to process.'

	EXEC [dbo].[RemoveDuplicateDataLogReadingsSingleSourceQuantity] @SourceIDToProcess, @QuantityIDToProcess

	DELETE FROM
		@PairsToProcess
	WHERE
		Ranking = @RowIDToProcess
		
	SET @PairsRemaining = @PairsRemaining-1
	UPDATE dbo.tmpRemoveDuplicatesProgress SET PairsLeftToProcess=@PairsRemaining

END
SET NOCOUNT OFF

SELECT dbo.FormatStatusMessage('Step 3c: Completed.')