
// CF 7-6

// Positions table

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

// Actual insert

INSERT INTO Positions
  SELECT 341288,
         NULL,
         EndDate,
         (SELECT COALESCE(MIN(StartDate), DATE '01/01/3000')
            FROM Positions AS P0
           WHERE P0.PCN = 341288
             AND P0.StartDate > P.StartDate)
    FROM Positions AS P
   WHERE P.PCN = 341288
     AND P.EndDate > DATE '12/05/1997'
     AND P.EndDate < DATE '01/01/3000'
     AND NOT EXISTS (SELECT *
                       FROM Positions AS P3
                      WHERE P3.PCN = 341288
                        AND P3.StartDate <= P.EndDate
                        AND P.EndDate < P3.EndDate);

