
/* Code Fragment 4.4 Extracting the relevant information. */

CREATE VIEW SAL_HISTORY
AS

SELECT SSN, SALARY_HOUR_RATE AS AMOUNT, HISTORY_START_DATE, HISTORY_END_DATE
FROM ZPSOS_COMPENSATION_HISTORY AS Z
WHERE CHRONOLOGY_KEY IN (	SELECT MAX(CHRONOLOGY_KEY)
				FROM ZPSOS_COMPENSATION_HISTORY AS Z2
				WHERE Z2.SSN = Z.SSN
				AND Z2.HISTORY_START_DATE < Z.HISTORY_END_DATE
				AND Z.HISTORY_START_DATE < Z2.HISTORY_END_DATE )
AND EMPLOYEE_FTE = 1.00
