ScottPletcher (4/18/2014)

---

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