-- ------------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- ------------------------------------------------
-- 
-- Temporal Joins in Oracle 7.3.2.
-- By   : Jose Alvin G. Gendrano
-- Date : July 18, 1997
--
-- NOTE : This version does not use UNION or UNIONALL
--        It instead uses the GREATEST and LEAST 
--        functions.
-- ------------------------------------------------

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   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.EndDate)) AND
                          NOT EXISTS (SELECT *
                                      FROM   Temp T2
                                      WHERE  T2.Salary = F.Salary AND
                                             ((T2.StartDate < F.StartDate AND
                                               F.StartDate <= T2.EndDate) OR
                                              (T2.StartDate <= L.Stop AND
                                               L.Stop < T2.EndDate)
                                             )
                   );


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

SELECT EmpSalary.Name, Salary, Title, 
       GREATEST (EmpSalary.StartDate, EmpTitle.StartDate),
       LEAST    (EmpSalary.Stop, EmpTitle.Stop)
FROM   EmpSalary, EmpTitle
WHERE  EmpSalary.Name = EmpTitle.Name AND
       GREATEST (EmpSalary.StartDate, EmpTitle.StartDate) <
       LEAST (EmpSalary.Stop, EmpTitle.Stop);

quit
