V_QUERY_EVENTSUMMARY

Query view used for event information derived from various aspects of events.

Fields

Field Field Type Null Description
ID uniqueidentifier System record ID
REGISTRANTCOUNT int yes Number of registrants
EXPENSEBUDGETEDAMOUNT money yes Total expense budgeted amount
EXPENSEACTUALAMOUNT money yes Total expense agreed amount
SUPPORTINGEVENTCOUNT int yes Number of supporting events
INVITEENOTYETINVITEDCOUNT int yes Number of invitees not yet invited
INVITEEINVITEDWITHNORESPONSECOUNT int yes Number of invitees invited with no response
INVITEEREGISTEREDCOUNT int yes Number of registered invitees
INVITEEDECLINEDCOUNT int yes Number of declined invitees
INVITEETOTAL int yes Total number of invitees
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
EXPENSEPAIDAMOUNT money yes Total expense paid amount
BASECURRENCYID uniqueidentifier yes Base currency ID
INVITEEATTENDEDCOUNT int yes
INVITEENOSHOWCOUNT int yes

Definition

Copy 
/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:28:44 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENTSUMMARY AS



                with INVITEES as
                (
                    select
                        ID,
                        EVENTID,
                        CONSTITUENTID,
                        INVITATIONSENTON,
                        DECLINED
                    from
                    (
                        select
                            ROW_NUMBER() over (partition by INVITEE.EVENTID, INVITEE.CONSTITUENTID order by INVITEE.INVITATIONSENTON desc) as ROWNUMBER,
                            INVITEE.ID,
                            INVITEE.EVENTID,
                            INVITEE.CONSTITUENTID,
                            INVITEE.INVITATIONSENTON,
                            INVITEE.DECLINED
                        from dbo.INVITEE
                    ) as INVITEELIST
                    where INVITEELIST.ROWNUMBER = 1
                ),
                EVENTINVITEESUMMARY as
                (
                    select
                        EVENTID,
                        count(*) [INVITEETOTAL],
                        sum([INVITEEPROPERTIES].[NOTYETINVITED]) [NOTYETINVITEDCOUNT],
                        sum([INVITEEPROPERTIES].[INVITEDWITHNORESPONSE]) [INVITEDWITHNORESPONSECOUNT],
                        sum([INVITEEPROPERTIES].[REGISTERED]) [REGISTEREDCOUNT],
                        sum([INVITEEPROPERTIES].[DECLINED]) [DECLINEDCOUNT],
                        sum([INVITEEPROPERTIES].[ATTENDED]) [ATTENDEDCOUNT],
                        sum([INVITEEPROPERTIES].[NOSHOW]) [NOSHOWCOUNT]
                    from
                    (
                        select
                            INVITEE.EVENTID,
                            case
                                when INVITEE.INVITATIONSENTON is null and INVITEE.DECLINED = 0 then 1
                                else 0
                            end [NOTYETINVITED],
                            case
                                when INVITEE.DECLINED = 0 and REGISTRANT.ID is null and INVITEE.INVITATIONSENTON is not null then 1
                                else 0
                            end [INVITEDWITHNORESPONSE],
                            case
                                when REGISTRANT.ID is not null then 1
                                else 0
                            end [REGISTERED],
                            case
                                when INVITEE.DECLINED = 1 then 1
                                else 0
                            end [DECLINED],
                            case
                                when REGISTRANT.ATTENDED = 1 then 1
                                else 0
                            end [ATTENDED],
                            case
                                when REGISTRANT.ATTENDED = 0 and REGISTRANT.USERMARKEDATTENDANCE = 1 then 1
                                else 0
                            end [NOSHOW]
                        from INVITEES as INVITEE
                        left join dbo.REGISTRANT on INVITEE.CONSTITUENTID = REGISTRANT.CONSTITUENTID and INVITEE.EVENTID = REGISTRANT.EVENTID
                    ) [INVITEEPROPERTIES]
                    group by [INVITEEPROPERTIES].[EVENTID]
                )
                select
                    EVENT.ID,
                    coalesce((select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID),0) [REGISTRANTCOUNT],
                    cast(coalesce((select sum(EVENTEXPENSE.BUDGETEDAMOUNT) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),0) as money) [EXPENSEBUDGETEDAMOUNT],
                    cast(coalesce((select sum(EVENTEXPENSE.ACTUALAMOUNT) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),0) as money) [EXPENSEACTUALAMOUNT],
                    coalesce((select count(ID) from dbo.EVENT as SUPPORTINGEVENT where SUPPORTINGEVENT.MAINEVENTID = EVENT.ID),0) [SUPPORTINGEVENTCOUNT],
                    coalesce([EVENTINVITEESUMMARY].[NOTYETINVITEDCOUNT], 0) [INVITEENOTYETINVITEDCOUNT],
                    coalesce([EVENTINVITEESUMMARY].[INVITEDWITHNORESPONSECOUNT], 0) [INVITEEINVITEDWITHNORESPONSECOUNT],
                    coalesce([EVENTINVITEESUMMARY].[REGISTEREDCOUNT], 0) [INVITEEREGISTEREDCOUNT],
                    coalesce([EVENTINVITEESUMMARY].[DECLINEDCOUNT], 0) [INVITEEDECLINEDCOUNT],
                    coalesce([EVENTINVITEESUMMARY].[INVITEETOTAL], 0) [INVITEETOTAL],
                    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
                    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
                    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
                    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
                    EVENT.DATEADDED,
                    EVENT.DATECHANGED,
                    EVENT.TSLONG,
                    coalesce((select sum(EVENTEXPENSE.AMOUNTPAID) from dbo.EVENTEXPENSE where EVENTEXPENSE.EVENTID = EVENT.ID),0) as [EXPENSEPAIDAMOUNT],
                    EVENT.BASECURRENCYID,
                    coalesce([EVENTINVITEESUMMARY].[ATTENDEDCOUNT], 0) as INVITEEATTENDEDCOUNT,
                    coalesce([EVENTINVITEESUMMARY].[NOSHOWCOUNT], 0) as INVITEENOSHOWCOUNT

                    /*#EXTENSION*/

                from dbo.EVENT 
                left join EVENTINVITEESUMMARY on EVENT.ID = EVENTINVITEESUMMARY.EVENTID
                left join dbo.CHANGEAGENT as ADDEDBY ON ADDEDBY.ID = EVENT.ADDEDBYID
                left join dbo.CHANGEAGENT as CHANGEDBY ON CHANGEDBY.ID = EVENT.CHANGEDBYID;