Workgroup History

-- Current status from EMPLOYEES table
    SELECT
        E.FILEKEY,
        E.LASTNAME,
        E.FIRSTNAME,
        E.IDNUM,
        w1.NAME AS WG1,
        w2.NAME AS WG2,
        w3.NAME AS WG3,
        w4.NAME AS WG4,
        w5.NAME AS WG5,
        --w6.NAME AS WG6,
        --w7.NAME AS WG7,
        E.WGEFFDATE AS EffectiveDate,
        'Current' AS RecordType
    FROM
        EMPLOYEES AS E
        LEFT JOIN WORKGROUP1 AS w1 ON E.WG1 = w1.WGNUM
        LEFT JOIN WORKGROUP2 AS w2 ON E.WG2 = w2.WGNUM
        LEFT JOIN WORKGROUP3 AS w3 ON E.WG3 = w3.WGNUM
        LEFT JOIN WORKGROUP4 AS w4 ON E.WG4 = w4.WGNUM
        LEFT JOIN WORKGROUP5 AS w5 ON E.WG5 = w5.WGNUM
        --LEFT JOIN WORKGROUP6 AS w6 ON E.WG6 = w6.WGNUM
        --LEFT JOIN WORKGROUP7 AS w7 ON E.WG7 = w7.WGNUM
UNION
ALL
    -- Historical status from EMPHOMEWGHIST table
    SELECT
        E.FILEKEY,
        E.LASTNAME,
        E.FIRSTNAME,
        E.IDNUM,
        w1.NAME AS WG1,
        w2.NAME AS WG2,
        w3.NAME AS WG3,
        w4.NAME AS WG4,
        w5.NAME AS WG5,
        --w6.NAME AS WG6,
        --w7.NAME AS WG7,
        H.EFFDATE AS EffectiveDate,
        'Historical' AS RecordType
    FROM
        EMPLOYEES AS E
        JOIN EMPHOMEWGHIST AS H ON E.FILEKEY = H.FILEKEY
        LEFT JOIN WORKGROUP1 AS w1 ON H.WG1 = w1.WGNUM
        LEFT JOIN WORKGROUP2 AS w2 ON H.WG2 = w2.WGNUM
        LEFT JOIN WORKGROUP3 AS w3 ON H.WG3 = w3.WGNUM
        LEFT JOIN WORKGROUP4 AS w4 ON H.WG4 = w4.WGNUM
        LEFT JOIN WORKGROUP5 AS w5 ON H.WG5 = w5.WGNUM
        --LEFT JOIN WORKGROUP6 AS w6 ON H.WG6 = w6.WGNUM
        --LEFT JOIN WORKGROUP7 AS w7 ON H.WG7 = w7.WGNUM
ORDER BY
    LASTNAME ASC,
    FIRSTNAME ASC,
    RecordType ASC,
    EffectiveDate DESC;