Query help

This query was written for our church several years ago to allow us to combine names correctly on address labels for certain bulk mailings (Dan Hart & Joan Mistele or Joe & Sue Smith). Any mailings we send out are casual except anytime we need to use this query because it includes titles. I have made a few attempts to eliminate titles but can't quite figure it out. Can anyone help me eliminate titles from this query?

SELECT DISTINCT

H.person_id,

CASE WHEN LEN(ISNULL(S.nick_name,''))>0 AND S.last_name=H.last_name AND ISNULL(S.title_luid,-1) NOT IN(308,310,10061,10046)

THEN ISNULL(HT.lookup_value,'Mr.')+' & '+ISNULL(ST.lookup_value,'Mrs.')+' '+H.nick_name+' '+H.last_name

WHEN LEN(ISNULL(S.nick_name,''))>0 AND S.last_name=H.last_name AND ISNULL(S.title_luid,-1) IN(308,310,10061,10046)

THEN ISNULL(HT.lookup_value,'Mr.')+' '+H.first_name+' & '+ST.lookup_value+' '+S.nick_name+' '+H.last_name

WHEN LEN(ISNULL(S.nick_name,''))>0 AND S.last_name<>H.last_name

THEN ISNULL(HT.lookup_value,'Mr.')+' '+H.nick_name+' '+H.last_name+' & '+ISNULL(ST.lookup_value,'Mrs.')+' '+S.nick_name+' '+S.last_name

ELSE ISNULL(HT.lookup_value+' ','')+H.nick_name+' '+H.last_name

END AS person_name,

H.last_name,

H.first_name+ISNULL(' & '+S.first_name,'') AS formal_sal,

H.nick_name+ISNULL(' & '+S.nick_name,'') AS casual_sal,

CA.street_address_1 AS add1,

CASE WHEN LEN(CA.street_address_2)>0 THEN CA.street_address_2 ELSE CA.city+', '+CA.state+' '+CA.postal_code END AS add2,

CASE WHEN LEN(CA.street_address_2)>0 THEN CA.city+', '+CA.state+' '+CA.postal_code ELSE '' END AS add3,

CA.street_address_1,

CA.city,

CA.state,

CA.postal_code

FROM

core_profile_member CPM

LEFT JOIN core_person H ON H.person_id=dbo.core_funct_familyHead(CPM.person_id)

LEFT JOIN core_person S ON S.person_id=dbo.core_funct_spouse(H.person_id,H.organization_id)

LEFT JOIN core_lookup HT ON HT.lookup_id=H.title_luid

LEFT JOIN core_lookup ST ON ST.lookup_id=S.title_luid

LEFT JOIN core_lookup HS ON HS.lookup_id=H.suffix_luid

LEFT JOIN core_person_address CPA ON CPA.person_id=H.person_id AND CPA.primary_address=1

LEFT JOIN core_address CA ON CA.address_id=CPA.address_id

WHERE CPM.profile_id=1361

ORDER BY H.last_name

More Content