USP_REPORT_EVENTREGISTRANTS

            CREATE procedure dbo.USP_REPORT_EVENTREGISTRANTS
            (
                @EVENTID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDESUBEVENTS bit = 0
            )
            as
                set nocount on;

                begin try
                    declare @TOTALREGISTRANTS int
                    declare @NEWREGISTRANTS int
                    declare @RECURRINGREGISTRANTS int


                    declare @EVENTS table
                    (
                        ID uniqueidentifier
                    )

                    declare @MAINEVENTID uniqueidentifier = (select MAINEVENTID from dbo.EVENT where ID = @EVENTID)

                    declare @EVENTSINHIERARCHY table (ID uniqueidentifier)
                    insert into @EVENTSINHIERARCHY select ID from dbo.UFN_EVENT_GETALLEVENTSINHIERARCHY(case when @MAINEVENTID is null then @EVENTID else @MAINEVENTID end)

                    if @INCLUDESUBEVENTS = 0
                    begin
                        insert into @EVENTS
                        select @EVENTID
                    end
                    else
                    begin
                        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
                    end

                    select 
                        @TOTALREGISTRANTS = count(distinct R.CONSTITUENTID) 
                    from 
                        dbo.REGISTRANT R
                    where 
                        R.EVENTID in (select ID from @EVENTS) and
                        R.CONSTITUENTID is not null

                    select
                        @TOTALREGISTRANTS = @TOTALREGISTRANTS + isnull(sum(LARGESTUNNAMEDGUESTCOUNTBYHOST.MAXGUESTCOUNTPERHOST), 0)
                    from (
                        select
                            isnull(max(UNNAMEDGUESTCOUNTBYHOSTANDEVENT.NUMBEROFGUESTS), 0) MAXGUESTCOUNTPERHOST
                        from (
                            select
                                HOST.CONSTITUENTID HOSTCONSTITUENTID, 
                                isnull(count(UNNAMEDGUEST.ID), 0) NUMBEROFGUESTS
                            from 
                                dbo.REGISTRANT UNNAMEDGUEST
                                inner join dbo.REGISTRANT HOST on UNNAMEDGUEST.GUESTOFREGISTRANTID = HOST.ID
                            where 
                                HOST.ID is not null
                                and UNNAMEDGUEST.CONSTITUENTID is null
                                and (
                                    HOST.EVENTID = @EVENTID
                                    or HOST.EVENTID in (select ID from @EVENTS)
                                )
                            group by HOST.CONSTITUENTID, HOST.EVENTID
                            ) UNNAMEDGUESTCOUNTBYHOSTANDEVENT
                        group by UNNAMEDGUESTCOUNTBYHOSTANDEVENT.HOSTCONSTITUENTID
                        ) LARGESTUNNAMEDGUESTCOUNTBYHOST

                    select 
                        @RECURRINGREGISTRANTS = count(distinct R.CONSTITUENTID)
                    from 
                        dbo.REGISTRANT R
                        inner join dbo.REGISTRANT CURRENTEVENT
                            on R.CONSTITUENTID = CURRENTEVENT.CONSTITUENTID and
                            CURRENTEVENT.EVENTID in (select ID from @EVENTS) and
                            CURRENTEVENT.CONSTITUENTID is not null
                    where 
                        R.EVENTID not in (select ID from @EVENTSINHIERARCHY) and
                        R.DATEADDED < CURRENTEVENT.DATEADDED

                    set @NEWREGISTRANTS = @TOTALREGISTRANTS - @RECURRINGREGISTRANTS

                    select 
                        @NEWREGISTRANTS as NEWREGISTRANT, 
                        @RECURRINGREGISTRANTS as RECURRINGREGISTRANT, 
                        @TOTALREGISTRANTS as TOTALREGISTRANTS

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;