UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETNOLONGERACTIVEDATES

CREATE function dbo.UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETNOLONGERACTIVEDATES()
returns table as return
    (
        --Get the total 'Active' post-expiration renewal window for each membership level, then add it to the expiration date of each membership transaction for that level.

        with ACTIVERENEWALRULES as
        (
            select
                ml.ID MEMBERSHIPLEVELID,
                sum(coalesce(INTERVALCODE + 1, 0)) as TOTALACTIVEWINDOWMONTHS
            from dbo.MEMBERSHIPLEVEL ml
                left join dbo.MEMBERSHIPLEVELRENEWAL mlr on mlr.MEMBERSHIPLEVELID = ml.ID and STATUSCODE = 0
            group by ml.ID
        )
        select mt.MEMBERSHIPID
            , mt.EXPIRATIONDATE
            , dateadd(month, arr.TOTALACTIVEWINDOWMONTHS, mt.EXPIRATIONDATE) as NOLONGERACTIVEDATE
            , mt.ID MEMBERSHIPTRANSACTIONID
        from dbo.MEMBERSHIPTRANSACTION mt
            left join ACTIVERENEWALRULES arr on arr.MEMBERSHIPLEVELID = mt.MEMBERSHIPLEVELID
    )