Cacher is the code snippet organizer for pro developers

USE staging
GO

DECLARE @historyId AS INT,
	   @participantId AS INT,
	   @actionId AS INT,
	   @historyTimestamp AS NVARCHAR(MAX),

	   @PreviousApplicationId AS INT = 0,
	   @name As Nvarchar(50),
	   @ordinal AS NVARCHAR(5),

	   @counterActionId7 AS INT = 0,
	   @counterActionId14 AS INT = 0,
	   @counterActionId15 AS INT = 0,
	   @counterActionId16 AS INT = 0,
	   @counterActionId19 AS INT = 0,

	   @counterInitial7 AS INT = 0,
	   @counterInitial14 AS INT = 0,
	   @counterInitial16 AS INT = 0,

	   @totalRecords AS INT = 0

DECLARE cursorHistory cursor 
FOR 
Select 
    ParticipantId, 
    HistoryTimeStamp, 
    ActionId, 
    HistoryId, 
    app.Name
from nghistory his
Inner Join mdApplications app On app.ApplicationsId = his.ParticipantId
where actionid In (7, 14, 16)
    And ParticipantId Is Not Null
	--and ParticipantId = 2877735
group by participantId, HistoryTimeStamp, ActionId, HistoryId, app.Name
Order by participantId, HistoryTimeStamp, ActionId, HistoryId, app.Name

OPEN cursorHistory
FETCH NEXT FROM cursorHistory
INTO	
    @participantId,
    @historyTimestamp,
    @actionId,
    @historyId, 
    @name

WHILE @@FETCH_STATUS = 0
    BEGIN
	   Print 'Processing history : '+ convert(nvarchar(255), @historyId) + ' , participantId : '+ convert(nvarchar(255),@participantId)+ ' and actionId : '+ convert(nvarchar(255),@actionId)

	   IF(@PreviousApplicationId != @participantId)
		  BEGIN
			 Print 'Reset counter'
			 SET @counterActionId7 = 0
			 SET @counterActionId14 = 0
			 SET @counterActionId15 = 0
			 SET @counterActionId16 = 0
			 SET @counterActionId19 = 0

			 Set @counterInitial7 = 0
			 Set @counterInitial14 = 0
			 Set @counterInitial16 = 0
		  END

	   IF(@ActionId = 7 AND @counterInitial7 = 0)
		  BEGIN
			 update ngHistory set info = 'Reference Requested From ' + @name
			 where historyId = @HistoryId

			 SET @counterInitial7 = @counterInitial7 + 1

			 Print 'Initial : ' + ' Reference Requested From ' + @name
		  END
	   ELSE IF(@ActionId = 7 and @PreviousApplicationId = @participantId)
		  BEGIN
			 SET  @counterActionId7 =  @counterActionId7 + 1

			 IF(@counterActionId7 = 1)
				BEGIN
				   SET @ordinal = 'st'
				END
			 ELSE IF(@counterActionId7 = 2)
				BEGIN
				   SET @ordinal = 'nd'
				END
			 ELSE IF(@counterActionId7 = 3)
				BEGIN
				   SET @ordinal = 'rd'
				END
			 ELSE IF(@counterActionId7 >= 4)
				BEGIN
				   SET @ordinal = 'th'
				END

			 update ngHistory set info = convert(nvarchar(255), @counterActionId7) +  @ordinal + ' Reminder Email for Reference From ' + @name
			 where historyId = @HistoryId

			 Print 'Reminder :' + convert(nvarchar(255), @counterActionId7) +  @ordinal + ' Reminder Email for Reference From ' + @name
		  END

	    

	   IF(@ActionId = 14 and @counterInitial14 = 0)
		  BEGIN
			 update ngHistory set info = 'Referee Details Requested From ' + @name
			 where historyId = @HistoryId

			 SET @counterInitial14 = @counterInitial14 + 1

			 Print 'Initial : Referee Details Requested From ' + @name
		  END
	   ELSE IF(@ActionId = 14 and @PreviousApplicationId = @participantId)
		  BEGIN
			 SET  @counterActionId14 =  @counterActionId14 + 1
		  
			 IF(@counterActionId14 = 1)
				BEGIN
				   SET @ordinal = 'st'
				END
			 ELSE IF(@counterActionId14 = 2)
				BEGIN
				   SET @ordinal = 'nd'
				END
			 ELSE IF(@counterActionId14 = 3)
				BEGIN
				   SET @ordinal = 'rd'
				END
			 ELSE IF(@counterActionId14 >= 4)
				BEGIN
				   SET @ordinal = 'th'
				END

			 update ngHistory set info = convert(nvarchar(255), @counterActionId14) +  @ordinal + ' Reminder Email for Referee Details From ' + @name
			 where historyId = @HistoryId

			 Print 'Reminder : ' + convert(nvarchar(255), @counterActionId14) +  @ordinal + ' Reminder Email for Referee Details From ' + @name
		  END

	   --IF(@ActionId = 15 and @PreviousApplicationId = @participantId)
		  --BEGIN
			 --SET  @counterActionId15 =  @counterActionId15 + 1

			 --update ngHistory set info = @counterActionId15 + 'some text'
			 --where historyId = @HistoryId
		  --END
	   --ELSE
		  --BEGIN
			 --update ngHistory set info = 'some text'
			 --where historyId = @HistoryId
		  --END
       
	   IF(@ActionId = 16 and @counterInitial16 = 0)
		  BEGIN
			 update ngHistory set info = 'Supplementary Information Requested from ' + @name
			 where historyId = @HistoryId

			 SET @counterInitial16 = @counterInitial16 + 1

			 Print 'Initial : Supplementary Information Requested from ' + @name
		  END
	   ELSE IF(@ActionId = 16 and @PreviousApplicationId = @participantId)
		  BEGIN
			 SET  @counterActionId16 =  @counterActionId16 + 1

			 IF(@counterActionId16 = 1)
				BEGIN
				   SET @ordinal = 'st'
				END
			 ELSE IF(@counterActionId16 = 2)
				BEGIN
				   SET @ordinal = 'nd'
				END
			 ELSE IF(@counterActionId16 = 3)
				BEGIN
				   SET @ordinal = 'rd'
				END
			 ELSE IF(@counterActionId16 >= 4)
				BEGIN
				   SET @ordinal = 'th'
				END

			 update ngHistory set info = convert(nvarchar(255), @counterActionId16) + @ordinal + ' Reminder Email for Supplementary Information from ' + @name
			 where historyId = @HistoryId

			 Print 'Reminder : ' +   convert(nvarchar(255), @counterActionId16) + @ordinal + ' Reminder Email for Supplementary Information from ' + @name
		  END
	    

	   --IF(@ActionId = 19 and @PreviousApplicationId = @participantId)
		  --BEGIN
			 --SET  @counterActionId19 =  @counterActionId19 + 1

			 --update ngHistory set info = @counterActionId19 + 'some text'
			 --where historyId = @HistoryId
		  --END
	   --ELSE
		  --BEGIN
			 --update ngHistory set info = 'some text'
			 --where historyId = @HistoryId
		  --END
	  
    SET @PreviousApplicationId = @participantId
	SET @totalRecords = @totalRecords + 1
    print ''

    FETCH NEXT FROM cursorHistory
	INTO	   
	   @participantId,
	   @historyTimestamp,
	   @actionId,
	   @historyId, 
	   @name

    END
	print 'Total Records: ' + convert(nvarchar(255), @totalRecords)
CLOSE cursorHistory

DEALLOCATE cursorHistory