-- ----------------------------------------------- 
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
-- 
-- Coalescing Using Iteration in ORACLE
-- By   : Jose Alvin G. Gendrano
-- Date : Feb. 21, 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 Temp;
CREATE TABLE Temp(Salary, StartDate, Stop) AS
SELECT Salary, StartDate, Stop
FROM   Employee
WHERE  Name = 'BOB';

BEGIN
  -- Enable OUTPUT, with buffer of 10000 bytes --
  DBMS_OUTPUT.ENABLE(10000);
  DBMS_OUTPUT.PUT_LINE('Started!');

  -- Loop until no more records are updated --
  LOOP
    UPDATE Temp T1
    SET    (T1.Stop) = (SELECT MAX(T2.Stop)
                        FROM   Temp T2
                        WHERE  T1.Salary = T2.Salary AND
                               T1.StartDate < T2.StartDate AND
                               T1.Stop >= T2.StartDate AND
                               T1.Stop < T2.Stop)

    WHERE  EXISTS (SELECT *
                   FROM   Temp T2
                   WHERE  T1.Salary = T2.Salary AND
                          T1.StartDate < T2.StartDate AND
                          T1.Stop >= T2.StartDate AND
                          T1.Stop < T2.Stop);


    DBMS_OUTPUT.PUT_LINE('After Update');

    -- Evaluate the Implicit Cursor attributes of the last
    -- executed SQL statement (UPDATE in this case)
    -- and exit when NO RECORDS were affected
    --     
    EXIT WHEN SQL%NOTFOUND;

  END LOOP;


  -- Delete Duplicate Records from the Table --
  DELETE FROM   Temp T1 
         WHERE  EXISTS (SELECT *
                        FROM   Temp T2
                        WHERE  T1.Salary = T2.Salary AND
                               ((T1.StartDate > T2.StartDate AND T1.Stop <= T2.Stop) OR
                                (T1.StartDate >= T2.StartDate AND T1.Stop < T2.Stop)));

  DBMS_OUTPUT.PUT_LINE('After Delete');
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   TEMP;

