USP_DATALIST_GROUPEDEVENTS_2

                CREATE procedure dbo.USP_DATALIST_GROUPEDEVENTS_2
                (
                    @MAINEVENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                set nocount on;

                declare @PATHTOSELECT hierarchyid = (select HIERARCHYPATH from dbo.EVENTHIERARCHY where ID = @MAINEVENTID);

                with [CTE] as
                (
                    select
                        EVENT.ID,
                        EVENT.NAME,
                        EVENT.STARTDATE,
                        EVENT.STARTTIME,
                        EVENT.ENDDATE,
                        EVENT.ENDTIME,
                        dbo.UFN_EVENTLOCATION_GETNAME(EVENT.EVENTLOCATIONID) as [LOCATION],
                        EVENT.CAPACITY,
                        coalesce((select count(ID) from dbo.REGISTRANT where EVENTID = EVENT.ID and WILLNOTATTEND = 0 and REGISTRANT.ISCANCELLED = 0), 0) as [WILLATTEND],
                        case when (select count(ID) from dbo.EVENT as [SUBEVENT] where [SUBEVENT].MAINEVENTID = EVENT.ID) > 0 then cast(1 as bit) else cast(0 as bit) end as [ISMAINEVENT],
                        EVENTHIERARCHY.HIERARCHYPATH,
                        case
                            when EVENT.ID = @MAINEVENTID then null
                            else (select [PARENTEVENTHIERARCHY].ID from dbo.EVENTHIERARCHY [PARENTEVENTHIERARCHY] where EVENTHIERARCHY.HIERARCHYPATH.GetAncestor(1) = [PARENTEVENTHIERARCHY].HIERARCHYPATH)
                        end as [PARENTID],
                        case when exists (select ID from dbo.EVENTAUCTION where EVENTAUCTION.ID = EVENT.ID) then cast(1 as bit) else cast(0 as bit) end as [ISAUCTION],
                        case when EVENT.APPEALID is not null then 1 else 0 end as [ISTEAMFUNDRAISINGEVENT],
                        EVENTMANAGEMENTLEVELINSTANCE.NAME as [LEVELNAME]

                    from 
                        dbo.EVENT     
                    left outer join
                        dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
                    left join dbo.EVENTMANAGEMENTOPTIONS on EVENT.ID = EVENTMANAGEMENTOPTIONS.EVENTID
                    left join dbo.EVENTMANAGEMENTLEVELINSTANCE on EVENTMANAGEMENTOPTIONS.EVENTMANAGEMENTLEVELINSTANCEID = EVENTMANAGEMENTLEVELINSTANCE.ID
                    where
                        EVENT.MAINEVENTID = (select isnull(MAINEVENTID, ID) from dbo.EVENT where ID = @MAINEVENTID) or
                        EVENT.ID = @MAINEVENTID
                )
                select 
                    [CTE].ID,
                    case when [CTE].LEVELNAME = '' or [CTE].LEVELNAME is null then [CTE].NAME else [CTE].NAME + ' - ' + [CTE].LEVELNAME end as NAME,
                    [CTE].STARTDATE,
                    [CTE].STARTTIME,
                    [CTE].ENDDATE,
                    [CTE].ENDTIME,
                    [CTE].LOCATION,
                    [CTE].CAPACITY,
                    [CTE].WILLATTEND,
                    --Percent of capacity

                    case
                        when [CTE].CAPACITY = 0 then 0
                        else cast(cast([CTE].WILLATTEND as decimal(10,2)) / cast([CTE].CAPACITY as decimal(10,2)) as decimal(10,2))        
                    end,
                    [CTE].ISMAINEVENT,
                    case when [CTE].ISMAINEVENT = 1 then N'RES:bullet_diamond' else N'' end,
                    [CTE].PARENTID,
                    row_number() over (partition by [CTE].PARENTID order by [CTE].HIERARCHYPATH) as [SEQUENCE],
                    [CTE].ISAUCTION,
                    dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, [CTE].ID) as [USERHASSITEACCESS],
                    [CTE].ISTEAMFUNDRAISINGEVENT
                from [CTE]
                where
                    ([CTE].PARENTID is null or [CTE].PARENTID in (select ID from [CTE]))
                    and ([CTE].HIERARCHYPATH.IsDescendantOf(@PATHTOSELECT) = 1 or @PATHTOSELECT is null)
                order by 
                    [CTE].ISMAINEVENT desc, [CTE].STARTDATE, [CTE].STARTTIME, [CTE].ENDDATE, [CTE].ENDTIME, [CTE].NAME;