USP_SMARTFIELD_SPONSORSHIPOPPORTUNITYPROJECTACTIVESPONSORS

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

    if @ASOF is not null
        insert into 
            @TABLE(ID) 
        select 
            SPONSORSHIPOPPORTUNITYID 
        from 
            dbo.SPONSORSHIP 
        inner join
            dbo.SPONSORSHIPOPPORTUNITYPROJECT on SPONSORSHIPOPPORTUNITYPROJECT.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID 
        where 
            SPONSORSHIP.DATECHANGED > @ASOF

        union 

        select 
            SPONSORSHIPOPPORTUNITYID 
        from 
            dbo.SPONSORSHIPAUDIT 
        inner join 
            dbo.SPONSORSHIPOPPORTUNITYPROJECT on SPONSORSHIPOPPORTUNITYPROJECT.ID = SPONSORSHIPAUDIT.SPONSORSHIPOPPORTUNITYID 
        where 
            SPONSORSHIPAUDIT.AUDITDATE > @ASOF

        union 

        select 
            ID 
        from 
            dbo.SPONSORSHIPOPPORTUNITYPROJECT 
        where 
            DATEADDED > @ASOF;
    else
        insert into 
            @TABLE(ID) 
        select 
            ID 
        from 
            dbo.SPONSORSHIPOPPORTUNITYPROJECT


    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 
        SOC.ID, 
        (
            select 
                count(*)
            from 
                dbo.SPONSORSHIP S
            where 
                S.SPONSORSHIPOPPORTUNITYID = SOC.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 SOC