Hourly Status History

-- Current status from EMPLOYEES table
    SELECT
        E.FILEKEY,
        E.LASTNAME,
        E.FIRSTNAME,
        E.IDNUM,
        ST.NAME AS Status,
        E.STATUSEFFDATE AS EffectiveDate,
        'Current' AS RecordType
    FROM
        EMPLOYEES AS E
        LEFT JOIN STATUSTYPES AS ST ON E.STATUS = ST.NUM
UNION
ALL
    -- Historical status from EMPSTATUSHIST table
    SELECT
        E.FILEKEY,
        E.LASTNAME,
        E.FIRSTNAME,
        E.IDNUM,
        ST.NAME AS Status,
        H.EFFDATE AS EffectiveDate,
        'Historical' AS RecordType
    FROM
        EMPLOYEES AS E
        JOIN EMPSTATUSHIST AS H ON E.FILEKEY = H.FILEKEY
        LEFT JOIN STATUSTYPES AS ST ON H.STATUS = ST.NUM
ORDER BY
    LASTNAME ASC,
    FIRSTNAME ASC,
    RecordType ASC,
    EffectiveDate DESC;