--------------------------------------------------------------------
--Performance Measurement Version
--As applied to the CoaDup DataSet (for comparison purposes)
--------------------------------------------------------------------
--Description : New Non Expanded Complex Query
--              Replaces the previous query, by a more efficient
--              Coalescing Operation
--Author      : Jose Alvin Gendrano
--Date        : Sept. 7, 1997
---------------------------------------------------------------------

SET SERVEROUTPUT ON;
DROP TABLE emp10COA;
CREATE TABLE emp10COA (EID       INTEGER,
                       FirstName CHAR(40),
                       LastName  CHAR(40),
                       StartDate DATE,
                       EndDate   DATE);

DECLARE 
  resultCount NUMBER := NULL;
  CURSOR emp10_cur is
    SELECT EID, FirstName, LastName, StartDate, EndDate
    FROM emp10
	ORDER BY EID, StartDate, EndDate;
  StartRec      emp10_cur%ROWTYPE;  -- The start of a Group 
  PrevRec       emp10_cur%ROWTYPE;  -- Previous Record 
  CurrRec       emp10_cur%ROWTYPE;  -- Current Record 
  last_timing   NUMBER := NULL; 
BEGIN

  -- Enable printing Put_Line Messages 
  DBMS_OUTPUT.PUT_LINE('Start');

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


  last_timing := DBMS_UTILITY.GET_TIME;

  IF NOT emp10_cur%ISOPEN
  THEN
    OPEN emp10_cur;
  END IF;

  FETCH emp10_cur INTO PrevRec;
  StartRec := PrevRec;   

  FETCH emp10_cur INTO CurrRec;

  WHILE emp10_cur%FOUND
  LOOP
    IF (StartRec.EID <> CurrRec.EID) OR 
       (PrevRec.EndDate + 1 < CurrRec.StartDate)
    THEN
      INSERT INTO emp10COA 
             VALUES (StartRec.EID, 
                     StartRec.FirstName,
                     StartRec.LastName,
                     StartRec.StartDate, 
                     PrevRec.EndDate);
      StartRec := CurrRec;
	  PrevRec  := StartRec;
    END IF;
	
	IF (CurrRec.EndDate > PrevRec.EndDate) THEN
       PrevRec := CurrRec;
	END IF;

    FETCH emp10_cur INTO CurrRec;
  END LOOP;

  INSERT INTO emp10COA 
  VALUES (StartRec.EID, 
          StartRec.FirstName,
          StartRec.LastName,
          StartRec.StartDate, 
          PrevRec.EndDate);

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

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

END;
/

quit
