USP_KPI_EVENTS_REGISTRANT_CAPACITY_VALUE

CREATE procedure dbo.USP_KPI_EVENTS_REGISTRANT_CAPACITY_VALUE
    @VALUE int output, 
    @ASOFDATE datetime, 
    @EVENTID uniqueidentifier,
    @INCLUDESUBEVENTS bit = 0
as

    set nocount on;
    set @VALUE=0

    declare @EVENTS table
    (
        ID uniqueidentifier
    )

    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 @ASOFDATE=dbo.UFN_DATE_GETLATESTTIME(@ASOFDATE);

    select @VALUE=COALESCE(count(ID),0) from dbo.REGISTRANT 

    where 
    EVENTID in (select ID from @EVENTS)
    AND DATEADDED <=@ASOFDATE;