• Try this:

    SELECT

    emp.eid, emp.agentid,

    info.emailid,

    mail.EmailAddr, mail.LName, mail.FName

    FROM employees AS emp

    outer apply

    (select top (1) emailid

    from employeeinfo ei

    where

    ei.agentid = emp.agentid and

    (stDate <= dateadd(dd, datediff(dd, 0, getdate()), 0)

    and

    endDate > dateadd(dd, datediff(dd, 0, getdate()), 0)

    )

    order by endDate DESC, modDt Desc

    ) AS info

    outer apply

    (select top 1 EmailAddr, LName, FName

    from employeemail x

    where CAST(info.emailID + '@company1.com' as varchar(255)) = x.mail

    ) As mail

    Where emp.agent = info.agent

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.