
// CF 5.3

// ZPOSCompHist (from UIS) table & data

CREATE TABLE ZPOSCompHist (
                        SSN             integer,
                        SalaryHourRate  integer,
                        ChronologyKey   integer,
                        EmployeeFTE     real,
                        StartDate       date,
                        EndDate         date
                          );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        52800,
                        1,
                        1.00,
                        DATE '07/01/1995',
                        DATE '03/01/1996'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        61300,
                        2,
                        1.00,
                        DATE '03/01/1996',
                        DATE '07/01/1996'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        72000,
                        3,
                        1.00,
                        DATE '07/01/1996',
                        DATE '07/15/1996'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        111223333,
                        83000,
                        4,
                        1.00,
                        DATE '07/15/1996',
                        DATE '01/01/3000'
                              );

INSERT INTO ZPOSCompHist VALUES (
                        444332222,
                        40100,
                        1,
                        1.00,
                        DATE '01/01/1997',
                        DATE '01/01/3000'
                              );

// SalHistory view

 CREATE VIEW SalHistory AS
               SELECT SSN,
                      SalaryHourRate,
                      StartDate,
                      EndDate
                 FROM ZPOSCompHist AS Z     
                WHERE ChronologyKey = (SELECT MAX(ChronologyKey)
                                         FROM ZPOSCompHist AS Z2
                                        WHERE Z2.SSN = Z.SSN)
                  AND EmployeeFTE = 1.00;

