pears.RemoveRD1Dups

CREATE PROCEDURE "pears"."RemoveRD1Dups"()
BEGIN
  DECLARE "MyCur" dynamic scroll cursor FOR SELECT "ID","D1_Date","d1_safetime","D1_User","D1_Type","appcli" FROM "R_D1" ORDER BY 2 ASC,3 ASC,4 ASC,5 ASC,6 ASC FOR UPDATE;
  DECLARE @ContactEventID CHAR(20);
  DECLARE @ContactDate DATE;
  DECLARE @LastContactDate DATE;
  DECLARE @ContactTime TIME;
  DECLARE @LastContactTime TIME;
  DECLARE @StaffID CHAR(20);
  DECLARE @LastStaffID CHAR(20);
  DECLARE @ClassCode CHAR(2);
  DECLARE @LastClassCode CHAR(2);
  DECLARE @PersonID CHAR(20);
  DECLARE @LastPersonID CHAR(20);
  DECLARE "Counter" BIGINT;
  DECLARE "DeleteCounter" INTEGER;
  SET "Counter" = 1;
  SET "DeleteCounter" = 0;
  message "string"('DeDup Started ',CURRENT TIMESTAMP) TO console;
  IF NOT EXISTS(SELECT * FROM "sysindex" WHERE "index_name" = 'XXXChangeCRSRD1DeDupIndexXXX') THEN
    message 'Creating Index' TO console;
    CREATE INDEX "XXXChangeCRSRD1DeDupIndexXXX" ON "R_D1"("D1_Date" ASC,"D1_SafeTime" ASC,"D1_User" ASC,"D1_Type" ASC,"appcli" ASC)
  END IF;
  OPEN "MyCur" WITH hold;
  fetch FIRST "MyCur" INTO @ContactEventID,@ContactDate,@ContactTime,@StaffID,@ClassCode,@PersonID;
  "ContactLoop":
  while(sqlcode = 0) loop
    IF(@LastContactDate = @ContactDate AND @LastContactTime = @ContactTime AND @LastStaffID = @StaffID AND @LastClassCode = @ClassCode AND @LastPersonID = @PersonID) THEN
      UPDATE "r_d1" SET "exclude" = 1 WHERE CURRENT OF "MyCur";
      SET "DeleteCounter" = "DeleteCounter"+1
    END IF;
    SET @LastContactDate = @ContactDate;
    SET @LastContactTime = @ContactTime;
    SET @LastStaffID = @StaffID;
    SET @LastClassCode = @ClassCode;
    SET @LastPersonID = @PersonID;
    IF(SELECT "remainder"("Counter",10000)) = 0 THEN
      message "string"("Counter",' Contact Events Checked') TO console
    END IF;
    SET "counter" = "counter"+1;
    fetch NEXT "MyCur" INTO @ContactEventID,@ContactDate,@ContactTime,@StaffID,@ClassCode,
      @PersonID
  END loop "ContactLoop";
  close "MyCur";
  message "string"("DeleteCounter",' Records Excluded') TO console;
  message "string"('DeDup Finished ',CURRENT TIMESTAMP) TO console
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."RemoveRD1Dups" IS 
{CREATE PROCEDURE RemoveRD1Dups()
BEGIN
  DECLARE MyCur cursor FOR SELECT ID, D1_Date, d1_safetime, D1_User, D1_Type, appcli FROM R_D1 ORDER BY 2,3,4,5,6 FOR UPDATE;
  DECLARE @ContactEventID CHAR(20);
  DECLARE @ContactDate DATE; 
  DECLARE @LastContactDate DATE;
  DECLARE @ContactTime TIME; 
  DECLARE @LastContactTime TIME;
  DECLARE @StaffID CHAR(20); 
  DECLARE @LastStaffID CHAR(20);
  DECLARE @ClassCode CHAR(2); 
  DECLARE @LastClassCode CHAR(2);
  DECLARE @PersonID CHAR(20);
  DECLARE @LastPersonID CHAR(20);
  DECLARE Counter BIGINT;
  DECLARE DeleteCounter	INT;
  SET Counter = 1;
  SET DeleteCounter = 0;
  Message string('DeDup Started ',CURRENT TIMESTAMP);
  IF NOT EXISTS (SELECT * FROM sysindex WHERE index_name = 'XXXChangeCRSRD1DeDupIndexXXX') THEN 
   message 'Creating Index';
   CREATE INDEX XXXChangeCRSRD1DeDupIndexXXX ON R_D1(D1_Date ASC , D1_SafeTime ASC , D1_User ASC , D1_Type ASC, appcli ASC )
  END IF;
  OPEN MyCur WITH hold;
  fetch FIRST MyCur INTO @ContactEventID, @ContactDate, @ContactTime, @StaffID, @ClassCode, @PersonID;
  ContactLoop:
  while (sqlcode = 0 ) loop
    IF ( @LastContactDate = @ContactDate AND @LastContactTime = @ContactTime AND @LastStaffID = @StaffID AND @LastClassCode = @ClassCode AND @LastPersonID = @PersonID )
   THEN
    UPDATE r_d1 SET exclude =1 WHERE CURRENT OF MyCur;
	SET DeleteCounter = DeleteCounter + 1;
    END IF;
    SET @LastContactDate = @ContactDate;
    SET @LastContactTime = @ContactTime;
    SET @LastStaffID = @StaffID;
    SET @LastClassCode = @ClassCode;
	SET @LastPersonID = @PersonID;
	IF (SELECT remainder(Counter,10000)) = 0 THEN 
	message string(Counter, ' Contact Events Checked');
		END IF;
    SET counter = counter + 1;
    fetch NEXT MyCur INTO @ContactEventID, @ContactDate, @ContactTime, @StaffID, @ClassCode, @PersonID;
  END loop ContactLoop;
  Close MyCur;
  Message string(DeleteCounter,' Records Excluded');
  Message string('DeDup Finished ',CURRENT TIMESTAMP);
END
}