Rate History

-- Current status from EMPLOYEES table
    SELECT
        E.FILEKEY,
        E.LASTNAME,
        E.FIRSTNAME,
        E.IDNUM,
        E.RATE,
        E.RATEEFFDATE AS EffectiveDate,
        'Current' AS RecordType
    FROM
        EMPLOYEES AS E
UNION ALL
    -- Historical status from EMPRAISEHIST table
    SELECT
        E.FILEKEY,
        E.LASTNAME,
        E.FIRSTNAME,
        E.IDNUM,
        H.RATE,
        H.EFFDATE AS EffectiveDate,
        'Historical' AS RecordType
    FROM
        EMPLOYEES AS E
        JOIN EMPRAISEHIST AS H ON E.FILEKEY = H.FILEKEY
ORDER BY
    LASTNAME ASC,
    FIRSTNAME ASC,
    RecordType ASC,
    EffectiveDate DESC;