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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".