V_QUERY_CONSTITUENTMAIL_SEASONALADDRESSOUTPUT
View used for seasonal address output for the correspondence process.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTID | uniqueidentifier | CONSTITUENTID | |
| CONSTITUENTNAME | nvarchar(154) | yes | Name |
| PRIMARYADDRESSEE | nvarchar(700) | yes | Primary addressee |
| PRIMARYSALUTATION | nvarchar(700) | yes | Primary salutation |
| NICKNAME | nvarchar(50) | Nickname | |
| PRIMARYCONTACT | nvarchar(154) | yes | Primary contact |
| ADDRESSBLOCK | nvarchar(150) | yes | Address Block |
| CITY | nvarchar(50) | yes | City |
| STATE | nvarchar(50) | yes | State |
| POSTCODE | nvarchar(12) | yes | ZIP |
| COUNTRY | nvarchar(100) | yes | Country |
| NUMBER | nvarchar(100) | yes | Phone |
| EMAILADDRESS | UDT_EMAILADDRESS | yes | |
| WEBADDRESS | UDT_WEBADDRESS | Web address | |
| SPOUSE | nvarchar(154) | yes | Spouse |
| PRIMARYBUSINESS | nvarchar(100) | yes | Primary education |
| PRIMARYEDUCATION | nvarchar(100) | yes | Primary business |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:35:27 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTMAIL_SEASONALADDRESSOUTPUT AS
select
CONSTITUENT.ID as CONSTITUENTID,
CONSTITUENT.NAME as CONSTITUENTNAME,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYADDRESSEE = 1) as PRIMARYADDRESSEE,
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.PRIMARYSALUTATION = 1) as PRIMARYSALUTATION,
CONSTITUENT.NICKNAME as NICKNAME,
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top (1)
CONTACT.NAME
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as PRIMARYCONTACT,
(select ADDRESSBLOCK from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as ADDRESSBLOCK,
(select CITY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as CITY,
(select STATE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as STATE,
(select POSTCODE from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as POSTCODE,
(select COUNTRY from dbo.UFN_ADDRESS_GETSEASONALADDRESS(CONSTITUENT.ID, getdate())) as COUNTRY,
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 and CONSTITUENT.ISGROUP = 0 then -- JohnLu 2007/08/20 Primary business relationships do not at present apply to groups
(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
left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1;