
// CF 7-23

// Incumbents table

CREATE TABLE Incumbents (
                        SSN             integer,
                        PCN             integer,
                        StartDate       date,
                        EndDate         date,
                        PRIMARY KEY (SSN, PCN)
                        );

// Positions table & data

CREATE TABLE Positions (
                        PCN             integer,
                        JobTitleCode1   integer,
                        StartDate       date,
                        EndDate         date,
                        PRIMARY KEY (PCN)
                       );
// JobTitles table & data

CREATE TABLE JobTitles (
                        JobTitleCode    integer,
                        JobTitle        char(66),
                        PRIMARY KEY (JobTitleCode)
                       );

// Actual modifications

INSERT INTO Incumbents
  SELECT 111223333,
         PCN,
         DATE '01/01/1998',
         EndDate
    FROM Incumbents
   WHERE SSN = 111223333
     AND StartDate < DATE '01/01/1998'
     AND EndDate > DATE '01/01/1998';

INSERT INTO Incumbents
  SELECT 111223333,
         PCN,
         StartDate,
         DATE '01/01/1997'
    FROM Incumbents
   WHERE SSN = 111223333
     AND StartDate < DATE '01/01/1997'
     AND EndDate > DATE '01/01/1997';

UPDATE Incumbents
   SET PCN = (SELECT PCN
                FROM Positions, JobTitles
               WHERE Positions.JobTitleCode1 = JobTitleCode
                 AND JobTitle = 'Director, Computer Center'
                 AND Positions.StartDate <= DATE '01/01/1997'
                 AND DATE '01/01/1997' < Positions.EndDate)
 WHERE SSN = 111223333
   AND StartDate < DATE '01/01/1998'
   AND EndDate > DATE '01/01/1997';

INSERT INTO Incumbents
SELECT 111223333,
       Positions.PCN,
       Positions.StartDate,
       Positions.EndDate
  FROM Incumbents, Positions, JobTitles
 WHERE SSN = 111223333
   AND Incumbents.StartDate <= DATE '01/01/1998'
   AND Incumbents.EndDate > DATE '01/01/1998'
   AND Positions.JobTitleCode1 = JobTitleCode
   AND JobTitle = 'Director, Computer Center'
   AND DATE '01/01/1997' <= Positions.StartDate
   AND Incumbents.StartDate <= Positions.StartDate
   AND Positions.EndDate < DATE '01/01/1998'
   AND Positions.EndDate < Incumbents.EndDate;

INSERT INTO Incumbents
SELECT 111223333,
       Positions.PCN,
       Positions.StartDate,
       DATE '01/01/1998'
  FROM Incumbents, Positions, JobTitles
 WHERE SSN = 111223333
   AND Incumbents.StartDate <= DATE '01/01/1998'
   AND Incumbents.EndDate > DATE '01/01/1998'
   AND Positions.JobTitleCode1 = JobTitleCode
   AND JobTitle = 'Director, Computer Center'
   AND DATE '01/01/1997' <= Positions.StartDate
   AND DATE '01/01/1998' < Positions.EndDate;

UPDATE Incumbents
SET StartDate = DATE '01/01/1997'
WHERE SSN = 111223333
  AND StartDate < DATE '01/01/1997'
  AND EndDate > DATE '01/01/1997';

UPDATE Incumbents
   SET EndDate = DATE '01/01/1998'
 WHERE SSN = 111223333
   AND StartDate < DATE '01/01/1998'
   AND EndDate > DATE '01/01/1998'
   AND NOT EXISTS (SELECT *
                     FROM Incumbents AS I2
                    WHERE Incumbents.SSN = I2.SSN
                      AND Incumbents.PCN = I2.PCN
                      AND Incumbents.StartDate < I2.EndDate
                      AND I2.StartDate < Incumbents.EndDate);

UPDATE Incumbents
   SET EndDate = (SELECT MIN(I2.StartDate)
                    FROM Incumbents AS I2
                   WHERE Incumbents.SSN = I2.SSN
                     AND Incumbents.PCN <> I2.PCN
                     AND Incumbents.StartDate < I2.StartDate)
 WHERE SSN = 111223333
   AND StartDate < DATE '01/01/1998'
   AND EndDate > DATE '01/01/1998'
   AND EXISTS (SELECT *
                 FROM Incumbents AS I2
                WHERE Incumbents.SSN = I2.SSN
                  AND Incumbents.PCN <> I2.PCN
                  AND Incumbents.StartDate < I2.EndDate
                  AND I2.StartDate < Incumbents.EndDate);

