V_QUERY_EVENTTOTALS

Query view used for event totals.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
TOTALAMOUNTRAISED	money	yes	Total amount raised
TOTALAVERAGERAISEDPERMONTH	money	yes	Average raised per month
TOTALAVERAGERAISEDPERWEEK	money	yes	Average raised per week
TOTALAVERAGERAISEDPERDAY	money	yes	Average raised per day
TEAMNUMBER	int		Number of teams
COMPANYNUMBER	int		Number of companies
HOUSEHOLDNUMBER	int		Number of households
TEAMNEWNUMBER	int		Number of new teams
COMPANYNEWNUMBER	int		Number of new companies
HOUSEHOLDNEWNUMBER	int		Number of new households
TEAMRETAINEDNUMBER	int		Number of retained teams
COMPANYRETAINEDNUMBER	int		Number of retained companies
HOUSEHOLDRETAINEDNUMBER	int		Number of retained households
PARTICIPANTNUMBER	int		Number of independent participants
PARTICIPANTRETAINEDNUMBER	int		Number of retained independent participants
PARTICIPANTNEWNUMBER	int		Number of new independent participants
SPONSORNUMBER	int		Number of sponsors
SPONSORRETAINEDNUMBER	int		Number of retained sponsors
SPONSORNEWNUMBER	int		Number of new sponsors
DONORNUMBER	int		Number of donors
DONORRETAINEDNUMBER	int		Number of retained donors
DONORNEWNUMBER	int		Number of new donors
TOTALCOMMUNICATION	int	yes	Number of communications sent
TOTALCOMMUNICATIONAVERAGEMONTH	int	yes	Average monthly communications sent
TOTALCOMMUNICATIONAVERAGEWEEK	int	yes	Average weekly communications sent
TOTALCOMMUNICATIONAVERAGEDAY	int	yes	Average daily communications sent
SPONSORCOMMUNICATIONSNUMBER	int		Communications sent by sponsors
COMPANYCOMMUNICATIONSNUMBER	int		Communications sent by companies
TEAMCOMMUNICATIONSNUMBER	int		Communications sent by teams
HOUSEHOLDCOMMUNICATIONSNUMBER	int		Communications sent by households
PARTICIPANTCOMMUNICATIONSNUMBER	int		Communications sent by participants
SponsorRevenue	money		Total sponsorship revenue
SponsorRevenueRetained	money		Sponsor revenue retained
PercSponsorRevenueRetained	money	yes	Percentage of sponsor revenue retained
DonorRevenue	money		Total Donation revenue
DonorRevenueRetained	money		Donation revenue retained
PercDonorRevenueRetained	money	yes	Percentage of donation revenue retained
RegistrantRevenue	money		Total registration revenue
RegistrantRevenueRetained	money		Registration revenue retained
PercRegistrantRevenueRetained	money	yes	Percentage of revenue 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:  2/1/2011 9:03:38 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.9.1001.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENTTOTALS AS



with 
[TeamNumber]
AS
(
    select TE.EVENTID
    ,TE.TYPECODE
    ,count(TFT.id) TEAMNUMBER
    ,count(AA.teamconstituentID) as TEAMRETAINEDNUMBER
    ,(count(TFT.id)-count(AA.teamconstituentID)) AS TEAMNEWNUMBER
  from dbo.TEAMFUNDRAISINGTEAM TFT
  inner join dbo.TEAMEXTENSION TE on TE.TEAMFUNDRAISINGTEAMID = TFT.ID
  inner join dbo.EVENTEXTENSION ET on TE.EVENTID = ET.EVENTID
  left outer join (
      select TE2.EVENTID, TE2.TEAMCONSTITUENTID,TE2.TYPECODE 
      from dbo.TEAMFUNDRAISINGTEAM TFT2
      inner join  dbo.TEAMEXTENSION TE2 on TE2.TEAMFUNDRAISINGTEAMID = TFT2.ID
  ) AA on TE.teamconstituentID = AA.teamconstituentID AND ET.PRIORYEAREVENTID = AA.EVENTID AND AA.TYPECODE=TE.TYPECODE 
  group by TE.EVENTID,TE.TYPECODE
)
,[PARTICIPANTNUMBER]
as
(
      SELECT EV.ID AS EVENTID
      ,COUNT(RG.ID) as PARTICIPANTNUMBER
      ,COUNT(AA.CONSTITUENTID) as PARTICIPANTRETAINEDNUMBER
      ,(COUNT(RG.ID)-COUNT(AA.CONSTITUENTID)) as PARTICIPANTNEWNUMBER
      FROM REGISTRANT RG   
      inner join dbo.CONSTITUENT C ON C.ID=RG.CONSTITUENTID 
      left join dbo.EVENT EV ON EV.ID=RG.EVENTID 
      left join dbo.EVENTEXTENSION ET ON ET.EVENTID = RG.EVENTID  
      left outer join (
           SELECT EV2.ID AS EVENTID
          ,C2.ID as CONSTITUENTID
          FROM REGISTRANT RG2  
          inner join dbo.CONSTITUENT C2 ON C2.ID=RG2.CONSTITUENTID 
          left join dbo.EVENT EV2 ON EV2.ID=RG2.EVENTID 
          left join dbo.EVENTEXTENSION ET2 ON ET2.EVENTID = RG2.EVENTID
          WHERE C2.ID not in (select TF2.CONSTITUENTID from dbo.TEAMFUNDRAISER TF2 where TF2.APPEALID=EV2.APPEALID)        
      ) AA ON AA.CONSTITUENTID=C.ID AND ET.PRIORYEAREVENTID=AA.EVENTID
      WHERE C.ID not in (select TF1.CONSTITUENTID from dbo.TEAMFUNDRAISER TF1 where TF1.APPEALID=EV.APPEALID)      
      GROUP BY EV.ID 
)
,[DONORS]
AS
(
          select distinct EV.ID  EVENTID
          ,ET.PRIORYEAREVENTID 
          ,R.CONSTITUENTID
          from dbo.REVENUE R
          inner join dbo.REVENUESPLIT RS ON R.ID = RS.REVENUEID
          inner join dbo.EVENT EV ON EV.APPEALID=R.APPEALID 
          inner join dbo.EVENTEXTENSION ET ON ET.EVENTID=EV.ID 
          where (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 1)
          OR (R.TRANSACTIONTYPECODE in (1,2) and RS.APPLICATIONCODE = 0) --pledge or recurring gift 

)
,[DONORSNUMBER]
AS
(
    select  A1.EVENTID
    ,COUNT(A1.CONSTITUENTID) AS DONORNUMBER
    ,COUNT(A2.CONSTITUENTID) AS DONORRETAINEDNUMBER
    ,(COUNT(A1.CONSTITUENTID)-COUNT(A2.CONSTITUENTID)) AS DONORNEWNUMBER
    FROM [DONORS] A1
    LEFT JOIN [DONORS] A2 ON A1.CONSTITUENTID=A2.CONSTITUENTID AND A1.PRIORYEAREVENTID=A2.EVENTID
    GROUP BY A1.EVENTID 
)
,[SPONSORSNUMBER]
AS
(
    SELECT ET.EVENTID
    ,COUNT(ES.ID) AS SPONSORNUMBER
     , COUNT(AA.SPONSORSHIPID) AS SPONSORRETAINEDNUMBER
     ,(COUNT(ES.ID)-COUNT(AA.SPONSORSHIPID)) AS SPONSORNEWNUMBER
    FROM dbo.EVENTSPONSOR ES
    INNER JOIN dbo.EVENTEXTENSION ET ON ET.EVENTID=ES.EVENTID 
    LEFT JOIN (
         SELECT ET2.EVENTID
        , ES2.ID AS SPONSORSHIPID
        FROM dbo.EVENTSPONSOR ES2
        INNER JOIN dbo.EVENTEXTENSION ET2 ON ET2.EVENTID=ES2.EVENTID 
    ) AA ON AA.SPONSORSHIPID=ES.ID AND AA.EVENTID=ET.PRIORYEAREVENTID 
    GROUP BY ET.EVENTID
)
,[SPONSORCOMMUNICATIONS_CTE]
AS
(
    SELECT ET.EVENTID
    ,sum(dbo.UFN_FAFGETTOTALCOMMUNICATIONS(ET.EVENTID, 
            dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(ES.CONSTITUENTID))) COMMUNICATIONSNUMBER
    FROM dbo.EVENTSPONSOR ES
    INNER JOIN dbo.EVENTEXTENSION ET ON ET.EVENTID=ES.EVENTID 
    group by ET.EVENTID
)
,[PARTICIPANTCOMMUNICATIONS_CTE]
AS
(
    select EV.ID EVENTID
    ,sum(dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EV.ID , 
                dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(C.ID))) COMMUNICATIONSNUMBER
    FROM REGISTRANT RG   
    inner join dbo.CONSTITUENT C ON C.ID=RG.CONSTITUENTID 
    inner join dbo.EVENT EV ON EV.ID=RG.EVENTID 
    group by EV.ID
)
,[COMPANYCOMMUNICATIONS_CTE]
AS
(
    select EVENTID
    ,sum(dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EVENTID , 
                dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(ID))) COMMUNICATIONSNUMBER
    from dbo.UFN_FAF_GROUPPARTICIPANTSLIST(NULL,NULL,2) 
    GROUP BY EVENTID
)
,[TEAMCOMMUNICATIONS_CTE]
AS
(
    select EVENTID
    ,sum(dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EVENTID , 
                dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(ID))) COMMUNICATIONSNUMBER
    from dbo.UFN_FAF_GROUPPARTICIPANTSLIST(NULL,NULL,1) 
    GROUP BY EVENTID
)
,[HOUSEHOLDCOMMUNICATIONS_CTE]
AS
(
    select EVENTID
    ,sum(dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EVENTID , 
                dbo.UFN_CLIENTUSERID_GET_BY_CONSTITUENT(ID))) COMMUNICATIONSNUMBER
    from dbo.UFN_FAF_GROUPPARTICIPANTSLIST(NULL,NULL,3) 
    GROUP BY EVENTID
)


select EV.ID
,dbo.UFN_REVENUE_GETEVENTTOTAL(EV.ID) TOTALAMOUNTRAISED
,dbo.UFN_REVENUE_GETEVENTTOTAL(EV.ID)/(CASE WHEN DateDiff(month,STARTDATE,ENDDATE)=0 THEN 1 ELSE DateDiff(month,STARTDATE,ENDDATE) END) TOTALAVERAGERAISEDPERMONTH
,dbo.UFN_REVENUE_GETEVENTTOTAL(EV.ID)/(CASE WHEN DateDiff(week,STARTDATE,ENDDATE)=0 THEN 1 ELSE DateDiff(week,STARTDATE,ENDDATE) END) TOTALAVERAGERAISEDPERWEEK
,dbo.UFN_REVENUE_GETEVENTTOTAL(EV.ID)/(CASE WHEN DateDiff(day,STARTDATE,ENDDATE)=0 THEN 1 ELSE DateDiff(day,STARTDATE,ENDDATE) END) TOTALAVERAGERAISEDPERDAY

,isnull(TNT.TEAMNUMBER,0) TEAMNUMBER
,isnull(TNC.TEAMNUMBER,0) COMPANYNUMBER
,isnull(TNH.TEAMNUMBER,0) HOUSEHOLDNUMBER

,isnull(TNT.TEAMNEWNUMBER,0) TEAMNEWNUMBER
,isnull(TNC.TEAMNEWNUMBER,0) COMPANYNEWNUMBER
,isnull(TNH.TEAMNEWNUMBER,0) HOUSEHOLDNEWNUMBER

,isnull(TNT.TEAMRETAINEDNUMBER,0) TEAMRETAINEDNUMBER
,isnull(TNC.TEAMRETAINEDNUMBER,0) COMPANYRETAINEDNUMBER
,isnull(TNH.TEAMRETAINEDNUMBER,0) HOUSEHOLDRETAINEDNUMBER

,isnull(PN.PARTICIPANTNUMBER,0) PARTICIPANTNUMBER
,isnull(PN.PARTICIPANTRETAINEDNUMBER,0) PARTICIPANTRETAINEDNUMBER
,isnull(PN.PARTICIPANTNEWNUMBER,0)  PARTICIPANTNEWNUMBER

,isnull(SN.SPONSORNUMBER,0) SPONSORNUMBER
,isnull(SN.SPONSORRETAINEDNUMBER,0) SPONSORRETAINEDNUMBER
,isnull(SN.SPONSORNEWNUMBER,0)  SPONSORNEWNUMBER

,isnull(DN.DONORNUMBER,0) DONORNUMBER
,isnull(DN.DONORRETAINEDNUMBER,0) DONORRETAINEDNUMBER
,isnull(DN.DONORNEWNUMBER,0) DONORNEWNUMBER

--Communications

,dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EV.ID, NULL) as TOTALCOMMUNICATION
,dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EV.ID, NULL)/(CASE WHEN DateDiff(month,STARTDATE,ENDDATE)=0 THEN 1 ELSE DateDiff(month,STARTDATE,ENDDATE) END) TOTALCOMMUNICATIONAVERAGEMONTH
,dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EV.ID, NULL)/(CASE WHEN DateDiff(week,STARTDATE,ENDDATE)=0 THEN 1 ELSE DateDiff(week,STARTDATE,ENDDATE) END) TOTALCOMMUNICATIONAVERAGEWEEK
,dbo.UFN_FAFGETTOTALCOMMUNICATIONS(EV.ID, NULL)/(CASE WHEN DateDiff(day,STARTDATE,ENDDATE)=0 THEN 1 ELSE DateDiff(day,STARTDATE,ENDDATE) END) TOTALCOMMUNICATIONAVERAGEDAY
,isnull(SPONSORCOMM.COMMUNICATIONSNUMBER,0) SPONSORCOMMUNICATIONSNUMBER
,isnull(COMPANYCOMM.COMMUNICATIONSNUMBER,0) COMPANYCOMMUNICATIONSNUMBER
,isnull(TEAMCOMM.COMMUNICATIONSNUMBER,0) TEAMCOMMUNICATIONSNUMBER
,isnull(HOUSEHOLDCOMM.COMMUNICATIONSNUMBER,0) HOUSEHOLDCOMMUNICATIONSNUMBER
,isnull(PARTCOMM.COMMUNICATIONSNUMBER,0) PARTICIPANTCOMMUNICATIONSNUMBER

--System Field

--sponsor revenue

,isnull(SponsorRevenue.VALUE,0) as SponsorRevenue
,isnull(SponsorRevenueRetained.VALUE,0) as SponsorRevenueRetained
,case when ISNULL(SponsorRevenue.VALUE,0)=0 then 0 else isnull(SponsorRevenueRetained.VALUE,0)/ISNULL(SponsorRevenue.VALUE,0) end as  PercSponsorRevenueRetained
--donor revenue

,isnull(DonorRevenue.VALUE,0) as DonorRevenue
,isnull(DonorRevenueRetained.VALUE,0) as DonorRevenueRetained
,case when ISNULL(DonorRevenue.VALUE,0)=0 then 0 else isnull(DonorRevenueRetained.VALUE,0)/ISNULL(DonorRevenue.VALUE,0) end as  PercDonorRevenueRetained
--registrant revenue

,isnull(RegistrantRevenue.VALUE,0) as RegistrantRevenue
,isnull(RegistrantRevenueRetained.VALUE,0) as RegistrantRevenueRetained
,case when ISNULL(RegistrantRevenue.VALUE,0)=0 then 0 else isnull(RegistrantRevenueRetained.VALUE,0)/ISNULL(RegistrantRevenue.VALUE,0) end as  PercRegistrantRevenueRetained

,ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION]
,ADDEDBY.USERNAME as [ADDEDBY_USERNAME]
,CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION]
,CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME]
,EV.DATEADDED
,EV.DATECHANGED
,EV.TSLONG
from dbo.EVENT EV
LEFT JOIN dbo.CHANGEAGENT AS [ADDEDBY] on [ADDEDBY].ID = EV.ADDEDBYID
LEFT JOIN dbo.CHANGEAGENT AS [CHANGEDBY] on [CHANGEDBY].ID = EV.CHANGEDBYID

LEFT JOIN [TeamNumber] TNT ON TNT.EVENTID=EV.ID AND TNT.TYPECODE=1
LEFT JOIN [TeamNumber] TNC ON TNC.EVENTID=EV.ID AND TNC.TYPECODE=2
LEFT JOIN [TeamNumber] TNH ON TNH.EVENTID=EV.ID AND TNH.TYPECODE=3

LEFT JOIN [PARTICIPANTNUMBER] PN ON PN.EVENTID=EV.ID 
LEFT JOIN [SPONSORSNUMBER] SN ON SN.EVENTID =EV.ID 
LEFT JOIN [DONORSNUMBER] DN ON DN.EVENTID=EV.ID 

LEFT JOIN [SPONSORCOMMUNICATIONS_CTE] SPONSORCOMM ON SPONSORCOMM.EVENTID=EV.ID 
LEFT JOIN [COMPANYCOMMUNICATIONS_CTE] COMPANYCOMM ON COMPANYCOMM.EVENTID=EV.ID 
LEFT JOIN [TEAMCOMMUNICATIONS_CTE] TEAMCOMM ON TEAMCOMM.EVENTID=EV.ID 
LEFT JOIN [HOUSEHOLDCOMMUNICATIONS_CTE] HOUSEHOLDCOMM ON HOUSEHOLDCOMM.EVENTID=EV.ID 
LEFT JOIN [PARTICIPANTCOMMUNICATIONS_CTE] PARTCOMM ON PARTCOMM.EVENTID=EV.ID 

--Smart Field Instance

--Total sponsorship revenue

left join dbo.UFN_SMARTFIELDINSTANCE_GETMONEYVALUES('634719A4-5FCD-4190-8617-763117A05834') SponsorRevenue on SponsorRevenue.ID=ev.ID 
--Sponsor revenue retained

left join dbo.UFN_SMARTFIELDINSTANCE_GETMONEYVALUES('77303602-6812-4A77-AEE9-D8A40AEEE669') SponsorRevenueRetained on SponsorRevenueRetained.ID=ev.ID 
--Donation revenue 

left join dbo.UFN_SMARTFIELDINSTANCE_GETMONEYVALUES('BE04BA97-701E-4903-8429-192A79B94DA7') DonorRevenue on  DonorRevenue.ID=ev.ID 
--Donation revenue retained

left join dbo.UFN_SMARTFIELDINSTANCE_GETMONEYVALUES('59509376-DEEF-41DF-B579-13F0AC529683') DonorRevenueRetained on DonorRevenueRetained.ID=ev.ID 
--registrant revenue 

left join dbo.UFN_SMARTFIELDINSTANCE_GETMONEYVALUES('0C72B1E7-550B-456C-A8DE-84FD10ADF2D5') RegistrantRevenue on  RegistrantRevenue.ID=ev.ID 
--registrant revenue retained

left join dbo.UFN_SMARTFIELDINSTANCE_GETMONEYVALUES('FAA4613E-AA17-455B-9EFA-E7EFE9315E17') RegistrantRevenueRetained on RegistrantRevenueRetained.ID=ev.ID