
// CF 7-14

// Incumbents table

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

// Positions table

CREATE TABLE Positions (
                        PCN             integer,
                        JobTitleCode1   integer,
                        StartDate       date,
                        EndDate         date,
                        PRIMARY KEY (PCN)
                       );

// Actual insert

INSERT INTO Incumbents
  SELECT 111223333,
         341288,
         DATE '01/01/1997',
         DATE '01/01/1998'
    FROM Incumbents
   WHERE NOT EXISTS (SELECT *
                       FROM Incumbents AS I2
                      WHERE Incumbents.SSN = I2.SSN
                        AND I2.StartDate < DATE '01/01/1998'
                        AND DATE '01/01/1997' < I2.EndDate)
     AND EXISTS (SELECT *
                   FROM Positions AS P
                  WHERE P.PCN = 341288
                    AND P.StartDate <= DATE '01/01/1997'
                    AND DATE '01/01/1997' < P.EndDate)
     AND EXISTS (SELECT *
                   FROM Positions AS P
                  WHERE P.PCN = 341288
                    AND P.StartDate <= DATE '01/01/1998'
                    AND DATE '01/01/1998' < P.EndDate)
     AND NOT EXISTS (SELECT P.PCN
                       FROM Positions AS P
                      WHERE P.PCN = 341288
                        AND DATE '01/01/1997' < P.EndDate
                        AND P.EndDate < '01/01/1998' 
                        AND NOT EXISTS (SELECT *
                                          FROM Positions AS P2
                                         WHERE P2.PCN = 341288
                                           AND P2.StartDate <= P.EndDate
                                           AND P.EndDate < P2.EndDate));

