UFN_QUERY_CORRESPONDENCEEMAILOUTPUT

CREATE function [dbo].[UFN_QUERY_CORRESPONDENCEEMAILOUTPUT]
(
  @PARAMETERSETID uniqueidentifier = null
)
returns table
as
return
(
  select 
    CONSTITUENT.ID as CONSTITUENTID,
    CONSTITUENT.NAME as CONSTITUENTNAME,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as ADDRESSEE,
    (select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as SALUTATION,
    CONSTITUENT.NICKNAME as NICKNAME,
    CONTACT.NAME as CONTACT,
    (select top 1 RELATIONSHIP.POSITION
     from dbo.RELATIONSHIP
     where RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
     and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID) as POSITION,
    PHONE.NUMBER,
    EMAILADDRESS.EMAILADDRESS,
    CONSTITUENT.WEBADDRESS,
    (select top 1 SPOUSE.NAME
     from dbo.RELATIONSHIP 
     inner join dbo.CONSTITUENT SPOUSE on RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID
     where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
     and RELATIONSHIP.ISSPOUSE = 1) as SPOUSE,
    (case when CONSTITUENT.ISORGANIZATION = 0 then
      (select top 1 ORG.KEYNAME
       from dbo.RELATIONSHIP 
       inner join CONSTITUENT ORG on RELATIONSHIP.RECIPROCALCONSTITUENTID = ORG.ID
       where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID
       and RELATIONSHIP.ISPRIMARYBUSINESS = 1)
     else null end) as PRIMARYBUSINESS,
    (select top 1 SCHOOL.NAME
     from dbo.EDUCATIONALHISTORY HISTORY 
     inner join dbo.EDUCATIONALINSTITUTION SCHOOL on HISTORY.EDUCATIONALINSTITUTIONID = SCHOOL.ID
     where HISTORY.ISPRIMARYRECORD = 1
     and HISTORY.CONSTITUENTID = CONSTITUENT.ID) as PRIMARYEDUCATION
  from dbo.CONSTITUENT
  inner join dbo.[UFN_ADDRESSPROCESS_EMAILS](3, @PARAMETERSETID) as EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID
  left join dbo.CONSTITUENT CONTACT on EMAILADDRESS.CONTACTID = CONTACT.ID
  left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
)