-- ----------------------------------------------- 
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
-- 
-- Coalescing Using Cursors in ORACLE
-- By   : Jose Alvin G. Gendrano
-- Date : Mar. 4, 1997
--
-- -----------------------------------------------

SET SERVEROUTPUT ON;

DROP   TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE (Name        char(30),
                       Salary      Integer,
                       Title       Char(30),
                       DateOfBirth DATE,
                       StartDate   DATE,
                       Stop        DATE);

INSERT INTO EMPLOYEE VALUES ('BOB', 60000, 'Assistant Provost', 
                                   TO_DATE('1945-04-09', 'YYYY-MM-DD'), 
                                   TO_DATE('1995-01-01', 'YYYY-MM-DD'),
                                   TO_DATE('1995-06-01', 'YYYY-MM-DD'));
 
INSERT INTO EMPLOYEE VALUES ('BOB', 70000, 'Assistant Provost', 
                                   TO_DATE('1945-04-09', 'YYYY-MM-DD'), 
                                   TO_DATE('1995-06-01', 'YYYY-MM-DD'),
                                   TO_DATE('1995-10-01', 'YYYY-MM-DD'));

INSERT INTO EMPLOYEE VALUES ('BOB', 70000, 'Provost', 
                                   TO_DATE('1945-04-09', 'YYYY-MM-DD'), 
                                   TO_DATE('1995-10-01', 'YYYY-MM-DD'),
                                   TO_DATE('1996-02-01', 'YYYY-MM-DD'));

INSERT INTO EMPLOYEE VALUES ('BOB', 70000, 'Professor', 
                                   TO_DATE('1945-04-09', 'YYYY-MM-DD'), 
                                   TO_DATE('1996-02-01', 'YYYY-MM-DD'),
                                   TO_DATE('1997-01-01', 'YYYY-MM-DD'));


DROP   TABLE TempRes;
CREATE TABLE TempRes(Salary, StartDate, EndDate) AS
SELECT Salary, StartDate, Stop
FROM   Employee
WHERE  Name = 'BOB';

DROP TABLE TempCOA;
CREATE TABLE TempCOA (salary       INTEGER,
                      StartDate    DATE,
                      EndDate      DATE);

DECLARE 
  CURSOR tempres_cur is
    SELECT salary, StartDate, EndDate
    FROM tempres;
  StartRec      tempres_cur%ROWTYPE;  -- The start of a Group 
  PrevRec       tempres_cur%ROWTYPE;  -- Previous Record 
  CurrRec       tempres_cur%ROWTYPE;  -- Current Record 
  last_timing   NUMBER := NULL; 
BEGIN

  -- ASSUMES THAT THE TUPLES ARE SORTED IN THE TABLE,
  -- Which is accomplished by the UNION operator
  -- instead of UNION ALL

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

  last_timing := DBMS_UTILITY.GET_TIME;

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

  FETCH tempres_cur INTO PrevRec;
  StartRec := PrevRec;   

  FETCH tempres_cur INTO CurrRec;

  WHILE tempRes_cur%FOUND
  LOOP
    IF (StartRec.salary <> CurrRec.salary) OR 
       (PrevRec.EndDate + 1 < CurrRec.StartDate)
    THEN
      INSERT INTO tempCOA 
             VALUES (StartRec.salary, StartRec.StartDate, PrevRec.EndDate);
      StartRec := CurrRec;
	  PrevRec  := StartRec;
    END IF;
	
	IF (CurrRec.EndDate > PrevRec.EndDate) THEN
       PrevRec := CurrRec;
	END IF;
    FETCH tempres_cur INTO CurrRec;
  END LOOP;

  INSERT INTO tempCOA 
         VALUES (StartRec.salary, StartRec.StartDate, PrevRec.EndDate);

  CLOSE tempres_cur;    
  DBMS_OUTPUT.PUT_LINE('[temp COALESCE] Elapsed Time  : ' || TO_CHAR(DBMS_UTILITY.GET_TIME - last_timing) ||
                       ' hundredths of a second.');
END;
/


BEGIN
  DBMS_OUTPUT.PUT_LINE(''); 
  DBMS_OUTPUT.PUT_LINE('ORIGINAL TABLE'); 
  DBMS_OUTPUT.PUT_LINE('--------------'); 
END;
/

SELECT Salary, StartDate, Stop
FROM   EMPLOYEE;


BEGIN
  DBMS_OUTPUT.PUT_LINE(''); 
  DBMS_OUTPUT.PUT_LINE('OUTPUT TABLE (AFTER COALESCING)'); 
  DBMS_OUTPUT.PUT_LINE('-------------------------------'); 
END;
/

SELECT * 
FROM   TEMPCoa;
/
