Active Status History

-- Current status from EMPLOYEES table
SELECT
    E.LASTNAME,
    E.FIRSTNAME,
    E.FILEKEY,
    E.IDNUM,
    CASE
        E.ACTIVESTATUS
        WHEN 0 THEN 'Active'
        WHEN 1 THEN 'Terminated'
        ELSE 'Unknown/Other'
    END AS ActiveStatus,
    CASE
        E.ACTIVESTATUS
        WHEN 0 THEN AST.NAME -- Use ACTIVESTATUSTYPES when Active
        WHEN 1 THEN TST.NAME -- Use TERMSTATUSTYPES when Terminated
        ELSE NULL
    END AS ActiveConditionName,
    E.ACTIVESTATUSEFFDATE AS EffectiveDate,
    CAST(NULL AS VARCHAR(255)) AS StatusComment,
    'Current' AS RecordType
FROM
    EMPLOYEES AS E
    LEFT JOIN ACTIVESTATUSTYPES AS AST ON E.ACTIVECONDITION = AST.NUM
    AND E.ACTIVESTATUS = 0
    LEFT JOIN TERMSTATUSTYPES AS TST ON E.TERMCONDITION = TST.NUM
    AND E.ACTIVESTATUS = 1
UNION
ALL -- Historical status from EMPACTSTATUSHIST table
SELECT
    E.LASTNAME,
    E.FIRSTNAME,
    E.FILEKEY,
    E.IDNUM,
    CASE
        H.STATUS
        WHEN 0 THEN 'Active'
        WHEN 1 THEN 'Terminated'
        ELSE 'Unknown/Other'
    END AS ActiveStatus,
    CASE
        H.STATUS
        WHEN 0 THEN AST_H.NAME -- Use ACTIVESTATUSTYPES when Active
        WHEN 1 THEN TST_H.NAME -- Use TERMSTATUSTYPES when Terminated
        ELSE NULL
    END AS ActiveConditionName,
    H.EFFDATE AS EffectiveDate,
    H.COMMENT AS StatusComment,
    'Historical' AS RecordType
FROM
    EMPLOYEES AS E
    JOIN EMPACTSTATUSHIST AS H ON E.FILEKEY = H.FILEKEY
    LEFT JOIN ACTIVESTATUSTYPES AS AST_H ON H.CONDITION = AST_H.NUM
    AND H.STATUS = 0
    LEFT JOIN TERMSTATUSTYPES AS TST_H ON H.CONDITION = TST_H.NUM
    AND H.STATUS = 1
ORDER BY
    LASTNAME ASC,
    FIRSTNAME ASC,
    EffectiveDate DESC;