V_QUERY_RESEARCHGROUPADDMEMBERS_ANYBUSINESS
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| RELATIONSHIPCONSTITUENTID | uniqueidentifier | ||
| BUSINESSNAME | nvarchar(154) | yes | |
| BUSINESSCITY | nvarchar(50) | yes | |
| BUSINESSSTATEID_ABBREVIATION | nvarchar(50) | yes | |
| JOBTITLE | nvarchar(100) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:10:11 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RESEARCHGROUPADDMEMBERS_ANYBUSINESS AS
select
BUSINESSRELATIONSHIP.ID,
BUSINESSRELATIONSHIP.RELATIONSHIPCONSTITUENTID,
BUSINESS.NAME BUSINESSNAME,
BUSINESSADDRESS.CITY BUSINESSCITY,
BUSINESSSTATE.ABBREVIATION as [BUSINESSSTATEID_ABBREVIATION],
BUSINESSJOBINFO.JOBTITLE
from dbo.RELATIONSHIP BUSINESSRELATIONSHIP
inner join dbo.CONSTITUENT on BUSINESSRELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
left join dbo.RELATIONSHIPJOBINFO BUSINESSJOBINFO on BUSINESSRELATIONSHIP.RELATIONSHIPSETID = BUSINESSJOBINFO.RELATIONSHIPSETID
and (BUSINESSJOBINFO.ENDDATE is null or BUSINESSJOBINFO.ENDDATE > getdate())
left outer join dbo.ADDRESS BUSINESSADDRESS on BUSINESSADDRESS.CONSTITUENTID = BUSINESSRELATIONSHIP.RECIPROCALCONSTITUENTID and BUSINESSADDRESS.ISPRIMARY = 1
left outer join dbo.STATE BUSINESSSTATE on BUSINESSSTATE.ID = BUSINESSADDRESS.STATEID
left join dbo.CONSTITUENT BUSINESS on BUSINESS.ID = BUSINESSRELATIONSHIP.RECIPROCALCONSTITUENTID
where CONSTITUENT.ISORGANIZATION = 1