USP_SMARTFIELD_MEMBERSHIPEXPIRATION

CREATE procedure dbo.[USP_SMARTFIELD_MEMBERSHIPEXPIRATION]
(
    @ASOF datetime,
    @CALCULATIONTYPE tinyint = 2,
    @SITES xml = null
)
as
    set nocount on;

    declare @TODAY datetime;
    declare @MEMBERSHIPFILTER table ([ID] uniqueidentifier primary key);

    set @TODAY = getdate();

    if @ASOF is not null 
        insert into 
            @MEMBERSHIPFILTER (ID) 
        select 
            [ID] 
        from 
            dbo.[MEMBERSHIP] 
        where 
            [MEMBERSHIP].[DATECHANGED] > @ASOF

        union

        select 
            [AUDITRECORDID] 
        from 
            dbo.[MEMBERSHIPAUDIT] 
        where 
            [MEMBERSHIPAUDIT].[AUDITDATE] > @ASOF

        union

        select 
            [MEMBERSHIPID] 
        from 
            dbo.[MEMBERSHIPTRANSACTION] 
        where 
            [MEMBERSHIPTRANSACTION].[DATECHANGED] > @ASOF

        union

        select 
            [MEMBERSHIPID] 
        from 
            dbo.[MEMBERSHIPTRANSACTIONAUDIT] 
        inner join 
            dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [MEMBERSHIPTRANSACTIONAUDIT].[MEMBERSHIPID] 
        where 
            [MEMBERSHIPTRANSACTIONAUDIT].[AUDITDATE] > @ASOF;

    else
        insert into 
            @MEMBERSHIPFILTER (ID) 
        select 
            [ID] 
        from dbo.[MEMBERSHIP];

    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 
        [F].[ID],
        case 
            when @CALCULATIONTYPE = 0 then datediff(dd, @TODAY, [MEMBERSHIP].[EXPIRATIONDATE]) -- days

            when @CALCULATIONTYPE = 1 then datediff(ww, @TODAY, [MEMBERSHIP].[EXPIRATIONDATE]) -- weeks

            when @CALCULATIONTYPE = 2 then datediff(mm, @TODAY, [MEMBERSHIP].[EXPIRATIONDATE]) -- months

            when @CALCULATIONTYPE = 3 then datediff(qq, @TODAY, [MEMBERSHIP].[EXPIRATIONDATE]) -- quarters

            when @CALCULATIONTYPE = 4 then datediff(yy, @TODAY, [MEMBERSHIP].[EXPIRATIONDATE]) -- years

        end as [EXPIRESIN]
    from
        @MEMBERSHIPFILTER as [F]
    inner join 
        dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [F].[ID]
    where 
        MEMBERSHIP.EXPIRATIONDATE is not null
        and
        (
            (@SITES is null) 
            or 
            exists(
                select 
                    CONSTITUENTSITE.ID 
                from 
                    CONSTITUENTSITE 
                inner join 
                    @SITESFILTER as SITES on SITES.ID = CONSTITUENTSITE.SITEID
                inner join
                    MEMBER on MEMBER.CONSTITUENTID = CONSTITUENTSITE.CONSTITUENTID
                where 
                    MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
            )
        )

    return 0;