V_QUERY_FUNDRAISINGGRUP_GOALS
Query view used for fundraising groups goals.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| GROUPID | uniqueidentifier | Group ID | |
| LastYearGroupID | uniqueidentifier | yes | Last Year Group ID |
| FUNDRAISINGTOTALAMOUNT | money | Group total amount goal | |
| NUMBEROFTEAM | int | yes | Group number of teams goal |
| PERCENTAGEOFTEAMRETAIN | decimal(13, 4) | yes | Group percentage of teams to retain |
| NUMBEROFTEAMMEMBERS | int | Group number of team members goal | |
| PERCENTAGEOFTEAMMEMBERSTORETAIN | decimal(13, 4) | yes | Group percentage of team members to retain |
| NUMBEROFPARTICIPANTSTORETAIN | int | Group number of participant recruit | |
| NUMBEROFCOMMUNICATIONSTOSEND | int | Group number of communications to send | |
| PEROFDONORS | decimal(13, 4) | yes | Group percentage of donors to retain |
| 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 |
| TotalAmountActual | money | Group total amount raised to date | |
| ParcOfTotalAmount | money | yes | Group percentage of fundraising goal to date |
| ParticipantGoalActual | int | Group number of participants recruited | |
| ParcParticipantGoal | money | yes | Group percentage of participants recruited |
| CommunicationSentActual | int | yes | Group number of communications sent |
| ParcCommunicationSent | money | yes | Group percentage of communications sent |
| TeamMemberActual | int | yes | Group number of team members actual |
| ParcTeamMember | money | yes | Group percentage of team member actual |
| TeamNumber | int | yes | Group number of teams actual |
| ParcTeamNumber | money | yes | Group percentage of team actual |
| TEAMRETAINEDNUMBER | int | yes | Group number of teams retained |
| ParcTeamNumberRetained | money | yes | Group percentage of teams retained |
| DONORSRETAINED | int | yes | Group number of donors retained |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:52:33 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_FUNDRAISINGGRUP_GOALS AS
select TE.ID
,TFT.ID GROUPID
,TEP.TEAMFUNDRAISINGTEAMID LastYearGroupID
,TFT.[GOAL] FUNDRAISINGTOTALAMOUNT
,CASE WHEN TE.TYPECODE=2 THEN TE.TEAMRECRUITMENTGOAL ELSE NULL END NUMBEROFTEAM
,CASE WHEN TE.TYPECODE=2 THEN TE.PCTTEAMSRETENSION * 100 ELSE NULL END PERCENTAGEOFTEAMRETAIN
,TE.TEAMMEMBERGOAL NUMBEROFTEAMMEMBERS
,TE.PCTTEAMMEMBERRETENSION * 100 PERCENTAGEOFTEAMMEMBERSTORETAIN
,TE.PARTICIPANTGOAL NUMBEROFPARTICIPANTSTORETAIN
,TE.COMMUNICATIONGOAL NUMBEROFCOMMUNICATIONSTOSEND
,TE.DONORRETENTIONGOAL * 100 PEROFDONORS
,GMN.MEMBERNUMBER
,GMN.MEMBERNUMBERNEW
,GMN.PARTMEMBERNUMBERRETAINED
,isnull(FTA.AMOUNTRAISED,0) TotalAmountActual
,(case when isnull(TFT.GOAL,0) = 0 then 0 else isnull(FTA.AMOUNTRAISED,0) / TFT.GOAL END) * 100 ParcOfTotalAmount
,isnull(dbo.UFN_FUNDRAISINGGROUP_PARTICIPANTNUMBER(TFT.ID),0) ParticipantGoalActual
,(case when isnull(TE.PARTICIPANTGOAL,0) = 0 then 0 else cast(isnull(dbo.UFN_FUNDRAISINGGROUP_PARTICIPANTNUMBER(TFT.ID),0) as money) / TE.PARTICIPANTGOAL End) * 100 ParcParticipantGoal
,dbo.UFN_FUNDRAISINGGROUP_COMMUNICATIONSENTNUMBER(TFT.ID) as CommunicationSentActual
,(case when ISNULL(TE.COMMUNICATIONGOAL,0) = 0 then 0 else cast(ISNULL(dbo.UFN_FUNDRAISINGGROUP_COMMUNICATIONSENTNUMBER(TFT.ID),0) as money) /te.COMMUNICATIONGOAL end) * 100 ParcCommunicationSent
,dbo.UFN_FUNDRAISINGGROUP_TEAMMEMBERNUMBER(TFT.ID) as TeamMemberActual
,(case when ISNULL(TE.TEAMMEMBERGOAL,0) = 0 then 0 else cast(ISNULL(dbo.UFN_FUNDRAISINGGROUP_TEAMMEMBERNUMBER(TFT.ID),0) as money) / TE.TEAMMEMBERGOAL end) * 100 ParcTeamMember
,T.TEAMNUMBER TeamNumber
,(case when isnull(TE.TEAMRECRUITMENTGOAL,0) = 0 then 0 else cast(isnull(T.TEAMNUMBER,0) as money) / TE.TEAMRECRUITMENTGOAL End) * 100 ParcTeamNumber
,T.TEAMRETAINEDNUMBER
,(case when isnull(T.TEAMRETAINEDNUMBER,0) = 0 then 0 else cast(isnull(T.TEAMNUMBER,0) as money) / T.TEAMRETAINEDNUMBER End) * 100 ParcTeamNumberRetained
,dbo.UFN_FUNDRAISINGGROUP_DONORRETAINEDNUMBER(TFT.ID) DONORSRETAINED
--System Field
,ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION]
,ADDEDBY.USERNAME as [ADDEDBY_USERNAME]
,CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION]
,CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME]
,TE.DATEADDED
,TE.DATECHANGED
,TE.TSLONG
from dbo.TEAMEXTENSION TE
inner join dbo.TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TE.TEAMFUNDRAISINGTEAMID
left join dbo.UFN_FUNDRAISINGGROUP_MEMBERSNUMBER() GMN ON GMN.GROUPID=TFT.ID
left join dbo.EVENT on EVENT.ID=TE.EVENTID
outer Apply dbo.UFN_FAF_GROUPTOTALSROLLUP(TE.EVENTID, TFT.ID) FTA
outer Apply dbo.UFN_COMPANY_TEAMS(TFT.ID) T
left join dbo.EVENTEXTENSION EE ON EVENT.ID = EE.EVENTID
left join dbo.EVENTEXTENSION EEP ON EEP.EVENTID = EE.PRIORYEAREVENTID
left join dbo.TEAMEXTENSION TEP ON TEP.EVENTID = EEP.EVENTID and TEP.TEAMCONSTITUENTID = TE.TEAMCONSTITUENTID
LEFT JOIN dbo.CHANGEAGENT AS [ADDEDBY] on [ADDEDBY].ID = TE.ADDEDBYID
LEFT JOIN dbo.CHANGEAGENT AS [CHANGEDBY] on [CHANGEDBY].ID = TE.CHANGEDBYID