-- ----------------------------------------------- 
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
-- 
-- Coalescing Using PURE SQL in ORACLE 7.3.2
-- By   : Jose Alvin G. Gendrano
-- Date : Feb. 25, 1997
--
-- -----------------------------------------------

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';


SELECT DISTINCT F.Salary, F.StartDate, L.Stop
FROM   Temp F, Temp L
WHERE  F.StartDate < L.Stop AND
       F.Salary = L.Salary AND
       NOT EXISTS (SELECT *
                   FROM   Temp M
                   WHERE  M.Salary = F.Salary AND
                          F.StartDate < M.StartDate AND
                          M.StartDate < L.Stop AND
                          NOT EXISTS (SELECT *
                                      FROM   Temp T1
                                      WHERE  T1.Salary = F.Salary AND
                                             T1.StartDate < M.StartDate AND
                                             M.StartDate <= T1.Stop)) AND
                          NOT EXISTS (SELECT *
                                      FROM   Temp T2
                                      WHERE  T2.Salary = F.Salary AND
                                             ((T2.StartDate < F.StartDate AND
                                               F.StartDate <= T2.Stop) OR
                                              (T2.StartDate <= L.Stop AND
                                               L.Stop < T2.Stop)
                                             )
                   );
                                      

