USP_REPORT_EVENTSINCLUDED

List of events included in the event revenue report.
Parameters

Parameter	Parameter Type	Mode
@EVENTID	uniqueidentifier	IN
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@INCLUDESUBEVENTS	bit	IN
Definition

Copy


            CREATE procedure dbo.USP_REPORT_EVENTSINCLUDED
            (
                @EVENTID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDESUBEVENTS bit = 0
            )
            as
            set nocount on;
            begin
                if @INCLUDESUBEVENTS = 0
                begin
                    select 
                        NAME as MAINEVENT,
                        null as SUBEVENT
                    from
                        dbo.EVENT
                    where
                        ID = @EVENTID
                end
                else begin
                    declare @EVENTS table (ID uniqueidentifier)
                    insert into @EVENTS
                        select RELATEDEVENT.ID
                        from dbo.EVENTHIERARCHY as RELATEDEVENT
                            inner join dbo.EVENTHIERARCHY as SOURCEEVENT on SOURCEEVENT.ID = @EVENTID
                        where RELATEDEVENT.HIERARCHYPATH.IsDescendantOf(SOURCEEVENT.HIERARCHYPATH) = 1

                    declare @MAINEVENTNAME nvarchar(100);
                    select @MAINEVENTNAME = NAME
                    from dbo.EVENT
                    where ID = @EVENTID;

                    select
                        @MAINEVENTNAME as MAINEVENT,
                        dbo.UDA_BUILDLIST(NAME) as SUBEVENT
                    from
                        dbo.EVENT
                    where
                        ID in (select ID from @EVENTS)
                        and
                        ID != @EVENTID
                end
            end