--EXEC MIG_VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_FOLDER_Sp '', ''
CREATE OR ALTER PROCEDURE MIG_VALIDATE_SUCCESSIVE_ENTRIES_IN_CHECK_FOLDER_Sp
(
	@STATUS INT = 0 OUT,
	@MESSAGE NVARCHAR(999) = NULL OUT
)
AS 
BEGIN
	DECLARE @CNT_FOLDERS INT
	
	SET @STATUS = 0
	SET @MESSAGE = NULL

	SELECT @CNT_FOLDERS = COUNT(*)
    FROM
    (
           SELECT 
                  ZZ_ID,
                  FOLDER_KEY_FROM, 
                  REVISION_FROM, 
                  FOLDER_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(FOLDER_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_FOLDER 
    ) T1
    JOIN
    (
           SELECT 
                  ZZ_ID,
                  FOLDER_KEY_FROM, 
                  REVISION_FROM, 
                  FOLDER_KEY_TO, 
                  REVISION_TO, 
                  CHECK_TYPE,
                  CHECKED_DATE,
                  CHECKED_TIME,
                  CAST(UPDATED_ON AS smalldatetime) AS UPDATED_ON,
                  ROW_NUMBER() OVER ( PARTITION BY UPPER(FOLDER_KEY_FROM) ORDER BY ZZ_ID ) AS ROW_COUNT
           FROM CHECK_FOLDER 
    ) T2
    ON  T2.FOLDER_KEY_FROM = T1.FOLDER_KEY_FROM 
    AND T2.REVISION_FROM = T1.REVISION_FROM
    AND T2.FOLDER_KEY_TO = T1.FOLDER_KEY_TO 
    AND T2.REVISION_TO = T1.REVISION_TO
    AND T2.CHECK_TYPE = T1.CHECK_TYPE
    AND T2.CHECKED_DATE = T1.CHECKED_DATE
    AND T2.CHECKED_TIME = T1.CHECKED_TIME
    AND T2.ZZ_ID <> T1.ZZ_ID
    AND T2.ROW_COUNT = (T1.ROW_COUNT + 1)
    AND T2.UPDATED_ON = T1.UPDATED_ON
    AND T2.ZZ_ID > T1.ZZ_ID

	IF @CNT_FOLDERS > 0
		BEGIN
			SET @STATUS = 1
			IF @MESSAGE IS NULL
				SET @MESSAGE = ''

			SET @MESSAGE = @MESSAGE + 'Consecutive entries in CHECK_FOLDER tables are similar and differing only in milliseconds (in terms of precision).. '
		END


	SELECT @STATUS, @MESSAGE
END

