V_QUERY_REGISTRANT_FUNDRAISINGGROUP
Query view used for fundraising groups information
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| PARENTTEAMID | uniqueidentifier | yes | Parent Team ID |
| EVENTID | uniqueidentifier | yes | Event record |
| EVENTNAME | nvarchar(100) | yes | Event name |
| GROUPTYPE | nvarchar(9) | yes | Group type name |
| GROUPNAME | nvarchar(100) | Group name | |
| GROUPWEBSITE | nvarchar(max) | yes | Group page URL |
| GROUPLEADERS | nvarchar(max) | yes | Group leader name |
| STARTDATE | datetime | Group start date | |
| ParentGroupName | nvarchar(100) | yes | Parent group name |
| TopGroupName | nvarchar(100) | yes | Top-level group name |
| SUBGROUPNUMBER | int | yes | Number of sub groups |
| SUBGROUPNEWNUMBER | int | yes | Number of new sub groups |
| SUBGROUPRETAINEDNUMBER | int | yes | Number of retained sub groups |
| PARTOFSUBGROUPRETAINED | int | yes | Sub group number of participant retained |
| MEMBERNUMBER | int | yes | Group number of members actual |
| MEMBERNUMBERNEW | int | yes | Group number of new members actual |
| PARTMEMBERNUMBERRETAINED | int | yes | Group percentage of members retained |
| COMMUNICATIONSENTACTUAL | int | yes | Group number of communications sent |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| COMMUNICATIONSENTSOCIAL | int | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:38:51 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REGISTRANT_FUNDRAISINGGROUP AS
select TFT.ID
,TFT.PARENTTEAMID
,TE.EVENTID
,EVENT.NAME EVENTNAME
,TE.TYPE GROUPTYPE
,TFT.NAME GROUPNAME
,dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(TFT.ID,TE.EVENTID,TE.TYPECODE) as GROUPWEBSITE
,(SELECT dbo.UDA_BUILDLIST(C.NAME)
FROM dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC (NOLOCK)
LEFT OUTER JOIN dbo.CONSTITUENT C WITH (NOLOCK)
ON C.ID = TC.CONSTITUENTID
WHERE TC.TEAMFUNDRAISINGTEAMID = TFT.ID
GROUP BY TC.TEAMFUNDRAISINGTEAMID) as GROUPLEADERS
,TFT.DATEADDED STARTDATE
,PTFT.NAME ParentGroupName
,CASE WHEN PPTFT.ID is not null then PPTFT.NAME when PPTFT.ID IS null and PTFT.ID is not null then PTFT.NAME else null end TopGroupName
,GROUPNUMBER.SUBGROUPNUMBER
,GROUPNUMBER.SUBGROUPNEWNUMBER
,GROUPNUMBER.SUBGROUPRETAINEDNUMBER
,GROUPNUMBER.PARTOFSUBGROUPRETAINED
,GMN.MEMBERNUMBER
,GMN.MEMBERNUMBERNEW
,GMN.PARTMEMBERNUMBERRETAINED
,(SELECT COUNT(ID)
FROM FAFCOMMUNICATIONSLOG
WHERE CLIENTUSERSID IN (
SELECT dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(CONSTITUENTID) FROM REGISTRANT
WHERE ID IN (SELECT REGISTRANTID FROM dbo.UFN_REGISTRANT_GROUPMEMBERLIST() WHERE GROUPID= TFT.ID AND TYPECODE=TE.TYPECODE) )
) as COMMUNICATIONSENTACTUAL
--System Field
,ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION]
,ADDEDBY.USERNAME as [ADDEDBY_USERNAME]
,CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION]
,CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME]
,TFT.DATEADDED
,TFT.DATECHANGED
,TFT.TSLONG
,(SELECT COUNT(*)
FROM [FAFEVENTSOCIALNETWORKPOST]
WHERE registrantid IN (SELECT REGISTRANTID FROM dbo.UFN_REGISTRANT_GROUPMEMBERLIST() WHERE GROUPID= TFT.ID AND TYPECODE=TE.TYPECODE)
) as COMMUNICATIONSENTSOCIAL
from TEAMFUNDRAISINGTEAM TFT
left join TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID=TFT.ID
left join dbo.TEAMFUNDRAISINGTEAM PTFT on PTFT.ID = TFT.PARENTTEAMID
left join dbo.TEAMFUNDRAISINGTEAM PPTFT ON PPTFT.ID = PTFT.PARENTTEAMID
left join dbo.UFN_FUNDRAISINGGROUP_SUBGROUPNUMBER() GROUPNUMBER ON GROUPNUMBER.GROUPID=TFT.ID and GROUPNUMBER.TYPECODE=1
left join dbo.UFN_FUNDRAISINGGROUP_MEMBERSNUMBER() GMN ON GMN.GROUPID=TFT.ID
left join dbo.EVENT on EVENT.ID=TE.EVENTID
LEFT JOIN dbo.CHANGEAGENT AS [ADDEDBY] on [ADDEDBY].ID = TFT.ADDEDBYID
LEFT JOIN dbo.CHANGEAGENT AS [CHANGEDBY] on [CHANGEDBY].ID = TFT.CHANGEDBYID