-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Bob was promoted to Director of the Computer
-- Center for 1997 (sequenced version).
-- By   : Bruce C. Huang
-- Date : Dec. 12, 1997
--
-- Oracle 7.3 cannot handle INSERT..VALUES..WHERE,
-- so the SQL92 format was modified to INSERT..
-- SELECT..WHERE
-- -----------------------------------------------
REM
REM Code Fragment 7.23 in Oracle 7.3
REM
INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333', PCN, TO_DATE('1998-01-01','YYYY-MM-DD'), END_DATE
FROM  INCUMBENTS
WHERE SSN = '111223333'
  AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
  AND END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD');

INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333', PCN, START_DATE, TO_DATE('1997-01-01','YYYY-MM-DD')
FROM  INCUMBENTS
WHERE SSN = '111223333'
  AND START_DATE < TO_DATE('1997-01-01','YYYY-MM-DD')
  AND END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET    PCN = ( SELECT PCN
               FROM   POSITIONS, JOB_TITLES
               WHERE  POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE
                 AND  JOB_TITLE = 'Director, Computer Center'
                 AND  POSITIONS.START_DATE <= TO_DATE('1997-01-01','YYYY-MM-DD')
                 AND  TO_DATE('1997-01-01','YYYY-MM-DD') < POSITIONS.END_DATE)
WHERE  SSN = '111223333'
  AND  START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
  AND  END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333', POSITIONS.PCN, POSITIONS.START_DATE, POSITIONS.END_DATE
FROM   INCUMBENTS, POSITIONS, JOB_TITLES
WHERE  SSN = '111223333'
   AND INCUMBENTS.START_DATE <= TO_DATE('1998-01-01','YYYY-MM-DD')
   AND INCUMBENTS.END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD')
   AND POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE
   AND JOB_TITLE = 'Director, Computer Center'
   AND POSITIONS.START_DATE >= TO_DATE('1997-01-01','YYYY-MM-DD')
   AND POSITIONS.START_DATE >= INCUMBENTS.START_DATE
   AND POSITIONS.END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD')
   AND POSITIONS.END_DATE > INCUMBENTS.END_DATE;

INSERT INTO INCUMBENTS
   ( SSN , PCN , START_DATE , END_DATE )
SELECT '111223333', POSITIONS.PCN, POSITIONS.START_DATE, 
       TO_DATE('1998-01-01','YYYY-MM-DD')
FROM   INCUMBENTS, POSITIONS, JOB_TITLES
WHERE  SSN = '111223333'
   AND INCUMBENTS.START_DATE <= TO_DATE('1998-01-01','YYYY-MM-DD')
   AND INCUMBENTS.END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD')
   AND POSITIONS.JOB_TITLE_CODE1 = JOB_TITLE_CODE
   AND JOB_TITLE = 'Director, Computer Center'
   AND POSITIONS.START_DATE >= TO_DATE('1997-01-01','YYYY-MM-DD')
   AND POSITIONS.END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET   START_DATE = TO_DATE('1997-01-01','YYYY-MM-DD')
WHERE SSN = '111223333'
  AND START_DATE < TO_DATE('1997-01-01','YYYY-MM-DD')
  AND END_DATE > TO_DATE('1997-01-01','YYYY-MM-DD');

UPDATE INCUMBENTS
SET   END_DATE = TO_DATE('1998-01-01','YYYY-MM-DD')
WHERE SSN = '111223333'
  AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
  AND END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD')
  AND NOT EXISTS ( SELECT *
                   FROM   INCUMBENTS I2
                   WHERE  INCUMBENTS.SSN = I2.SSN
                      AND INCUMBENTS.PCN = I2.PCN
                      AND INCUMBENTS.START_DATE < I2.END_DATE
                      AND I2.START_DATE < INCUMBENTS.END_DATE);

UPDATE INCUMBENTS
SET   END_DATE = (SELECT MIN(I2.START_DATE)
                  FROM  INCUMBENTS I2
                  WHERE INCUMBENTS.SSN = I2.SSN
                    AND INCUMBENTS.PCN != I2.PCN
                    AND INCUMBENTS.START_DATE < I2.START_DATE)
WHERE SSN = '111223333'
  AND START_DATE < TO_DATE('1998-01-01','YYYY-MM-DD')
  AND END_DATE > TO_DATE('1998-01-01','YYYY-MM-DD')
  AND NOT EXISTS ( SELECT *
                   FROM   INCUMBENTS I2
                   WHERE  INCUMBENTS.SSN = I2.SSN
                      AND INCUMBENTS.PCN != I2.PCN
                      AND INCUMBENTS.START_DATE < I2.END_DATE
                      AND I2.START_DATE < INCUMBENTS.END_DATE);

