USP_SMARTFIELD_SPONSORSHIPOPPORTUNITYCHILDACTIVESPONSORS

CREATE procedure dbo.USP_SMARTFIELD_SPONSORSHIPOPPORTUNITYCHILDACTIVESPONSORS
(
    @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.SPONSORSHIPOPPORTUNITYCHILD on SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID 
        where SPONSORSHIP.DATECHANGED > @ASOF

        union 

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

        union 

        select 
            ID 
        from 
            dbo.SPONSORSHIPOPPORTUNITYCHILD 
        where 
            DATEADDED > @ASOF;

        else
        insert into 
            @TABLE(ID) 
        select 
            ID 
        from 
            dbo.SPONSORSHIPOPPORTUNITYCHILD


    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(S.ID)
            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