-- ------------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- ------------------------------------------------
-- 
-- Temporal Joins in Oracle 7.3.2.
-- By   : Jose Alvin G. Gendrano
-- Date : Feb. 9, 1997
--
-- NOTE : Uses UNION.
--        UNION automatically removes duplicates
--        and sorts on the most "obvious" key,
--        all the fields combined. The significance
--        of the fields during the sort operation
--        increases from the leftmost field to the
--        rightmost field.
-- ------------------------------------------------

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


/* Divide the Employee Table into two Tables */

DROP   TABLE EmpSalary; 
CREATE TABLE EmpSalary(Name, Salary, StartDate, Stop) AS
SELECT DISTINCT F.Name, F.Salary, F.StartDate, L.Stop
FROM   Employee F, Employee L
WHERE  F.StartDate < L.Stop AND
       F.Salary = L.Salary AND
       NOT EXISTS (SELECT *
                   FROM   Employee M
                   WHERE  M.Salary = F.Salary AND
                          F.StartDate < M.StartDate AND
                          M.StartDate < L.Stop AND
                          NOT EXISTS (SELECT *
                                      FROM   Employee T1
                                      WHERE  T1.Salary = F.Salary AND
                                             T1.StartDate < M.StartDate AND
                                             M.StartDate <= T1.Stop)) AND
                          NOT EXISTS (SELECT *
                                      FROM   Employee 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)
                                             )
                   );


DROP   TABLE EmpTitle;  
CREATE TABLE EmpTitle(Name, Title, StartDate, Stop) AS
SELECT Name, Title, StartDate, Stop
FROM   Employee;

SELECT EmpSalary.Name, Salary, Title, EmpSalary.StartDate, EmpSalary.Stop
FROM   EmpSalary, EmpTitle
WHERE  EmpSalary.Name = EmpTitle.Name AND
       EmpTitle.StartDate <= EmpSalary.StartDate AND
       EmpSalary.Stop <= EmpTitle.Stop
UNION
SELECT EmpSalary.Name, Salary, Title, EmpSalary.StartDate, EmpTitle.Stop
FROM   EmpSalary, EmpTitle
WHERE  EmpSalary.Name = EmpTitle.Name AND
       EmpSalary.StartDate > EmpTitle.StartDate AND
       EmpTitle.Stop < EmpSalary.Stop AND
       EmpSalary.StartDate < EmpTitle.Stop
UNION
SELECT EmpSalary.Name, Salary, Title, EmpTitle.StartDate, EmpSalary.Stop
FROM   EmpSalary, EmpTitle
WHERE  EmpSalary.Name = EmpTitle.Name AND
       EmpTitle.StartDate > EmpSalary.StartDate AND
       EmpSalary.Stop < EmpTitle.Stop AND
       EmpTitle.StartDate < EmpSalary.Stop
UNION
SELECT EmpSalary.Name, Salary, Title, EmpTitle.StartDate, EmpTitle.Stop
FROM   EmpSalary, EmpTitle
WHERE  EmpSalary.Name = EmpTitle.Name AND
       EmpTitle.StartDate >= EmpSalary.StartDate AND
       EmpTitle.Stop <= EmpSalary.Stop;
exit





