
/* Code Fragment 6.11 Bob was promoted to Director of the Computer Center. */

INSERT INTO INCUMBENTS
SELECT '111-22-3333', '908739', GETDATE(), END_DATE
FROM INCUMBENTS
WHERE SSN='111-22-3333'
      AND START_DATE <= GETDATE()
      AND END_DATE > GETDATE()

UPDATE INCUMBENTS
SET END_DATE = GETDATE()
WHERE SSN='111-22-3333'
      AND PCN <> '908739'
      AND START_DATE < GETDATE()
      AND END_DATE > GETDATE()

UPDATE INCUMBENTS 
SET PCN = '908739'
WHERE SSN='111-22-3333'
      AND START_DATE > GETDATE()
