SELECT p01.PIN #Pin,
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)