USP_SPONSORSHIP_ACTIVESPONSORCOUNT

CREATE procedure dbo.USP_SPONSORSHIP_ACTIVESPONSORCOUNT
(
    @ASOF datetime,
    @SITES xml = null
)
as
    declare @TABLE  table (ID uniqueidentifier);

    if @ASOF is not null
        insert into 
            @TABLE
        select distinct 
            SPONSORSHIP.CONSTITUENTID
        from 
            dbo.SPONSORSHIP
        where 
            dbo.SPONSORSHIP.STATUSCODE=1
            and 
            DATEADDED>@ASOF

        union

        select 
            CONSTITUENTID 
        from 
            dbo.SPONSORSHIPAUDIT AUD 
        inner join 
            dbo.CONSTITUENT on AUD.CONSTITUENTID = CONSTITUENT.ID 
        where 
            AUD.AUDITDATE > @ASOF

        union

        select 
            ID 
        from 
            dbo.SPONSOR 
        where 
            DATEADDED > @ASOF;
    else
        insert into 
            @TABLE
        select 
            ID
        from 
            dbo.SPONSOR;

    declare @SITESFILTER table(ID uniqueidentifier primary key);
    insert into 
        @SITESFILTER(ID) 
    select distinct
        T.c.value('(SITEID)[1]','uniqueidentifier')
    FROM
        @SITES.nodes('/SITES/ITEM') T(c)

    select 
        T.ID,
        (
            select 
                COUNT(S.ID) 
            from 
                dbo.SPONSORSHIP S
            where 
                S.CONSTITUENTID = T.ID 
                and 
                S.STATUSCODE=1
                and
                (
                    (@SITES is null) 
                    or 
                    ((select COUNT(ID) from @SITESFILTER) = 0)
                    or 
                    exists(
                        select 
                            CONSTITUENTSITE.ID 
                        from 
                            CONSTITUENTSITE 
                        inner join 
                            @SITESFILTER as SITES on SITES.ID = CONSTITUENTSITE.SITEID 
                        where 
                            CONSTITUENTSITE.CONSTITUENTID = S.CONSTITUENTID
                    )
                )
        ) as VAL 
    from 
        @TABLE T