--------------------------------------------------------------------
--Performance Measurement Version
--------------------------------------------------------------------
--Description : Original COMPLEX SQL Query for Non Expanded Tables
--Author      : Jose Alvin Gendrano
--Date        : Mar 3, 1997
--Update      : August 6, 1997
--------------------------------------------------------------------


SET SERVEROUTPUT ON;

DROP TABLE emp10RES;
CREATE TABLE emp10RES (EID       INTEGER,
                       StartDate DATE,
                       EndDate   DATE);

DROP INDEX TempIndex;
CREATE INDEX TempIndex ON emp10RES(EID, StartDate, EndDate) tablespace 
jagindex;


DECLARE 
  last_timing NUMBER := NULL; 
  resultCount NUMBER := NULL;
BEGIN
  /* Enable printing Put_Line Messages */
  DBMS_OUTPUT.ENABLE(10000);
  DBMS_OUTPUT.PUT_LINE('Start');

  INSERT INTO emp10RES
  SELECT E1.EID, E1.StartDate, E1.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate <= E1.StartDate AND
         E1.EndDate <= E2.EndDate
  UNION ALL
  SELECT E1.EID, E1.StartDate, E2.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E1.StartDate > E2.StartDate AND
         E2.EndDate < E1.EndDate AND
         E1.StartDate < E2.EndDate
  UNION ALL
  SELECT E1.EID, E2.StartDate, E1.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate > E1.StartDate AND
         E1.EndDate < E2.EndDate AND
         E2.StartDate < E1.EndDate
  UNION ALL
  SELECT E1.EID, E2.StartDate, E2.EndDate
  FROM   emp10 E1, emp10 E2
  WHERE  E1.MID = E2.EID AND
         E1.Salary > E2.Salary AND
         E2.StartDate >= E1.StartDate AND
         E2.EndDate <= E1.EndDate AND
         NOT (E2.Startdate = E1.StartDate AND
              E2.EndDate = E1.EndDate);

  SELECT COUNT(*) INTO resultCount FROM emp10res;
  DBMS_OUTPUT.PUT_LINE('# of Records to Operate on : ' || resultCount);

  last_timing := DBMS_UTILITY.GET_TIME;

  /* Loop until no more records are updated */
  LOOP
    UPDATE emp10res T1
    SET    (T1.EndDate) = (SELECT MAX(T2.EndDate)
                        FROM   emp10res T2
                        WHERE  T1.EID = T2.EID AND
                               T1.StartDate < T2.StartDate AND
                               T1.EndDate >= (T2.StartDate - 1) AND
                               T1.EndDate < T2.EndDate)

    WHERE  EXISTS (SELECT *
                   FROM   emp10res T2
                   WHERE  T1.EID = T2.EID AND
                          T1.StartDate < T2.StartDate AND
                          T1.EndDate >= (T2.StartDate - 1) AND
                          T1.EndDate < T2.EndDate);

    EXIT WHEN SQL%NOTFOUND;

  END LOOP;
  /* Delete Duplicate Records from the Table */
  DELETE FROM   emp10res T1 
         WHERE  EXISTS (SELECT *
                        FROM   emp10res T2
                        WHERE  T1.EID = T2.EID AND
                               ((T1.StartDate > T2.StartDate AND T1.EndDate <= T2.EndDate) OR
                                (T1.StartDate >= T2.StartDate AND T1.EndDate < T2.EndDate)));

  DBMS_OUTPUT.PUT_LINE('[emp10 COALESCE ITERATIVE] Elapsed Time  : ' || TO_CHAR(DBMS_UTILITY.GET_TIME - last_timing) ||
                       ' hundredths of a second.');

  SELECT COUNT(*) INTO resultCount FROM emp10Res;
  DBMS_OUTPUT.PUT_LINE('Resulting Records : ' || resultCount);

END;
/

DROP TABLE emp10RES;

quit
