
/* Code Fragment 5.21 Coalesce INCUMBENTS while removing duplicates (entirely in SQL). */

CREATE TABLE Tempo 
(
	SSN CHAR(11),
	PCN CHAR(6), 
	START_DATE DATETIME, 
	END_DATE DATETIME
)

INSERT Tempo
SELECT *
FROM INCUMBENTS

SELECT DISTINCT F.SSN, F.PCN, F.START_DATE, L.END_DATE
FROM Tempo AS F, Tempo AS L
WHERE F.START_DATE < L.END_DATE
AND F.SSN = L.SSN
AND F.PCN = L.PCN
AND NOT EXISTS (SELECT *
		FROM Tempo AS M
		WHERE M.SSN = F.SSN
		AND M.PCN = F.PCN
		AND F.START_DATE < M.START_DATE
		AND M.START_DATE < L.END_DATE
		AND NOT EXISTS (SELECT *
				FROM Tempo AS T1
				WHERE T1.SSN = F.SSN
				AND T1.PCN = F.PCN
				AND T1.START_DATE < M.START_DATE
				AND M.START_DATE <= T1.END_DATE))
AND NOT EXISTS (SELECT *
		FROM Tempo AS T2
		WHERE T2.SSN = F.SSN
		AND T2.PCN = F.PCN
		AND ((T2.START_DATE < F.START_DATE
			AND F.START_DATE <= T2.END_DATE)
		OR (T2.START_DATE <= L.END_DATE
			AND L.END_DATE < T2.END_DATE)))
