p01.firstname #Firstname,

p01.lastname #Lastname,

email.TELENO as #Email

FROM pw001p01 p01

LEFT JOIN PW001P0T email

ON email.PIN = p01.PIN

AND email.TELETYPE = 6

AND email.TELEPRIORITY IS NOT NULL

AND NOT EXISTS (

SELECT 1

FROM PW001P0T t

WHERE t.PIN = email.PIN

AND t.TELETYPE = email.TELETYPE

AND t.TELEPRIORITY IS NOT NULL

AND (

t.TELEPRIORITY < email.TELEPRIORITY

OR t.TELEPRIORITY = email.TELEPRIORITY

AND t.SEQUENCENO < email.SEQUENCENO

)

)

WHERE

DATEPART(dd, p01.BIRTHDATE) = DATEPART(dd, GETDATE()) AND DATEPART(mm, p01.BIRTHDATE) = DATEPART(mm, GETDATE())

AND NULLIF(email.TELENO, '') IS NOT NULL

AND (p01.Employmentenddate='' or p01.Employmentenddate is NULL)