V_QUERY_WPSOCIAL

Fields

Field	Field Type	Null
ID	uniqueidentifier
SOURCE	nvarchar(100)	yes
FULLNAME	nvarchar(600)	yes
GENDER	nvarchar(7)	yes
LOCATION	nvarchar(2048)	yes
OCCUPATION	nvarchar(1024)	yes
COMPANY	nvarchar(2048)	yes
SOCIALMEDIARATING	int	yes
SOCIALMEDIARATINGDESCRIPTION	varchar(158)
INFLUENCERATING	int	yes
OUTREACHRATING	int	yes
DATEADDED	datetime	yes
DATECHANGED	datetime	yes
TSLONG	bigint	yes
ADDEDBY_APPLICATION	nvarchar(200)	yes
ADDEDBY_USERNAME	nvarchar(128)	yes
CHANGEDBY_APPLICATION	nvarchar(200)	yes
CHANGEDBY_USERNAME	nvarchar(128)	yes

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  9/30/2015 12:58:26 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_WPSOCIAL AS



                select
                    CONSTITUENT.ID,
                    WPS.SOURCE,
          WPS.FULLNAME,
          WPS.GENDER,
          WPS.LOCATION,
          WPS.OCCUPATION,
          WPS.COMPANY,
                    case WPS.SOCIALMEDIARATING  when -1 then NULL else WPS.SOCIALMEDIARATING  end SOCIALMEDIARATING,
          case WPS.SOCIALMEDIARATING
            when 0 then 'Unconnected - We were unable to find social media accounts linked to this constituent.'
            when 1 then 'Social Starter - Limited social users, linked to one account, and have very little influence.'
            when 2 then 'Multi-Networker - Passive social users with a presence on more than one platform.'
            when 3 then 'Socially Connected - Moderate social users whose engagement is limited to a smaller social circle and is primarily for personal entertainment and interaction.'
            when 4 then 'Content Cultivator - Heavy social users who are active but do not carry the network size or impact of a true influencer.'
            when 5 then 'Social Influencer - Heavy social users who often share content and reach a large audience.'
            else '' end SOCIALMEDIARATINGDESCRIPTION,
                    case WPS.INFLUENCERATING  when -1 then NULL else WPS.INFLUENCERATING  end INFLUENCERATING,
                    case WPS.OUTREACHRATING  when -1 then NULL else WPS.OUTREACHRATING  end OUTREACHRATING,
                    WPS.DATEADDED,
                    WPS.DATECHANGED,
                    WPS.TSLONG,
                    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
                    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
                    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
                    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME
                    /*#EXTENSION*/ 
                from
          dbo.CONSTITUENT --We want to display social media accounts even if a WPSOCIAL record doesn't exist, and we will nee da constituent ID to link those

          left join    dbo.WPSOCIAL WPS on CONSTITUENT.ID = WPS.ID
                    left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = WPS.ADDEDBYID
                    left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = WPS.CHANGEDBYID