# 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"