# From Joe Celko's column in DBMS magazine, May 1998, page 25
# Corrected, then altered for INCUMBENTS
# Discussed in November, 1998 column in DBPD Online

db2 "DROP TABLE INCUMBENTS"

db2 "CREATE TABLE INCUMBENTS(SSN INTEGER NOT NULL, PCN INTEGER NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE)"

db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-01'), DATE('1997-01-04'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-02'), DATE('1997-01-05'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-04'), DATE('1997-01-06'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-06'), DATE('1997-01-10'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-09'), DATE('1997-01-10'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-09'), DATE('1997-01-10'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-12'), DATE('1997-01-16'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-13'), DATE('1997-01-15'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-14'), DATE('1997-01-15'))"
db2 "INSERT INTO INCUMBENTS VALUES(1, 1, DATE('1997-01-17'), DATE('1997-01-18'))"

db2 "DROP VIEW V1"

db2 "CREATE VIEW V1 (SSN, PCN, START_DATE, END_DATE) AS SELECT F.SSN, F.PCN, F.START_DATE, L.END_DATE FROM INCUMBENTS AS F, INCUMBENTS AS L, INCUMBENTS AS E WHERE F.END_DATE <= L.END_DATE AND F.SSN = L.SSN AND F.SSN = E.SSN AND F.PCN = L.PCN AND F.PCN = E.PCN GROUP BY F.SSN, F.PCN, F.START_DATE, L.END_DATE HAVING COUNT(CASE WHEN (E.START_DATE < F.START_DATE AND F.START_DATE <= E.END_DATE) OR (E.START_DATE <= L.END_DATE AND L.END_DATE < E.END_DATE) THEN 1 END) = 0"

db2 "DROP TABLE Temp"

db2 "CREATE TABLE Temp(SSN INTEGER, PCN INTEGER, START_DATE DATE, END_DATE DATE)"

db2 "INSERT INTO Temp SELECT SSN, PCN, START_DATE, MIN(END_DATE) FROM V1 GROUP BY SSN, PCN, START_DATE"

db2 "SELECT * FROM Temp"