-- create the field in table emArea
/*IF EXISTS
   (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'emArea'
            AND COLUMN_NAME = 'MCLSortOrder'
            )

  )
   ALTER TABLE [dbo].[emArea]
      DROP COLUMN [MCLSortOrder]

GO

ALTER TABLE dbo.emArea 
	ADD MCLSortOrder INT
GO
*/

DECLARE CalculatedOccurrenceCursor CURSOR FOR
	SELECT dbo.emOccurrCalcSource.Target, dbo.emOccurrence.SummaryStatus
	FROM         dbo.Reference INNER JOIN
						  dbo.emOccurrence ON dbo.Reference.RefId = dbo.emOccurrence.MCLPreferredRefFk INNER JOIN
						  dbo.emArea ON dbo.emOccurrence.AreaFk = dbo.emArea.AreaId RIGHT OUTER JOIN
						  dbo.emOccurrCalcSource ON dbo.emOccurrence.OccurrenceId = dbo.emOccurrCalcSource.Source
	WHERE     (NOT (dbo.emOccurrence.MCLPreferredRefFk IS NULL))
	GROUP BY dbo.emOccurrCalcSource.Target, dbo.emOccurrence.SummaryStatus

DECLARE @OccurrenceId INT, 
		@SummaryStatus INT,
		@count INT,
		@MultipleCount INT

SET @count = 0
SET @MultipleCount = 0

OPEN CalculatedOccurrenceCursor

FETCH FROM CalculatedOccurrenceCursor 
	INTO 
		@OccurrenceId,
		@SummaryStatus

WHILE @@FETCH_STATUS = 0 
BEGIN

	
	DECLARE @ReferenceId INT,
			@IdInMCL Varchar(10)
	
	DECLARE  CalcSourceCursor SCROLL CURSOR FOR
		SELECT dbo.emOccurrence.MCLPreferredRefFk,  dbo.Reference.IdInMCL /*, dbo.emOccurrCalcSource.emOccurCalcSourceId, dbo.emOccurrCalcSource.Source, dbo.emOccurrCalcSource.Target, 
						  dbo.emOccurrence.CalculatedFlag, dbo.emOccurrence.SummaryStatus, 
						  dbo.emArea.MCLCode, dbo.emArea.MCLSortOrder, dbo.emArea.Unit, dbo.emArea.EMCode*/
		FROM         dbo.Reference INNER JOIN
						  dbo.emOccurrence ON dbo.Reference.RefId = dbo.emOccurrence.MCLPreferredRefFk INNER JOIN
						  dbo.emArea ON dbo.emOccurrence.AreaFk = dbo.emArea.AreaId RIGHT OUTER JOIN
						  dbo.emOccurrCalcSource ON dbo.emOccurrence.OccurrenceId = dbo.emOccurrCalcSource.Source
		WHERE     (NOT (dbo.emOccurrence.MCLPreferredRefFk IS NULL))
					AND
					dbo.emOccurrCalcSource.Target = @OccurrenceId
					AND 
					dbo.emOccurrence.SummaryStatus = @SummaryStatus
		ORDER BY dbo.emOccurrCalcSource.Target, dbo.emOccurrence.SummaryStatus, CAST(dbo.Reference.IdInMCL AS INT), dbo.emArea.MCLSortOrder

	OPEN CalcSourceCursor

	FETCH FIRST FROM CalcSourceCursor INTO @ReferenceId, @IdInMCL

	
	PRINT CAST(@count AS VARCHAR)
	PRINT @OccurrenceId
	PRINT @ReferenceId
	
	UPDATE emOccurrence SET MCLPreferredRefFk = @ReferenceId WHERE OccurrenceId = @OccurrenceId 

	SET @count = @count + 1

	CLOSE CalcSourceCursor
	DEALLOCATE CalcSourceCursor

	FETCH NEXT FROM CalculatedOccurrenceCursor
		INTO 	@OccurrenceId,
				@SummaryStatus

END

PRINT 'processed records: ' + CAST(@count AS VARCHAR)


CLOSE CalculatedOccurrenceCursor
DEALLOCATE CalculatedOccurrenceCursor
GO

