UFN_MEMBERSHIPTRANSACTION_CALCULATEMEMBERSHIPSTATUSCODE

CREATE function dbo.UFN_MEMBERSHIPTRANSACTION_CALCULATEMEMBERSHIPSTATUSCODE (
    @ID uniqueidentifier,
    @ASOF date
)
returns table
as return (
    select
        case
            when MEMBERSHIPPROGRAM.PROGRAMTYPECODE IN (1,2) then
              MEMBERSHIP.STATUSCODE -- For Recurring/Sustaining and Lifetime programs, leave the STATUSCODE the same. A future story will change the status code for these program types.

            when MEMBERSHIPTRANSACTION.ACTIONCODE = 4 then  -- Drop

                1  -- Cancelled

            when @ASOF <= MEMBERSHIPTRANSACTION.EXPIRATIONDATE then
                0  -- Active

            when @ASOF > dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIPTRANSACTION.EXPIRATIONDATE) then
                MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE
            when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID is not null and MLR1.INTERVALCODE = 12 or @ASOF <= dateadd(month, MLR1.INTERVALCODE+1, MEMBERSHIPTRANSACTION.EXPIRATIONDATE)) then
                MLR1.STATUSCODE  -- When the current date is within the first rules period

            when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID is not null and MLR2.INTERVALCODE = 12 or @ASOF <= dateadd(month, (MLR1.INTERVALCODE+1 + MLR2.INTERVALCODE+1), MEMBERSHIPTRANSACTION.EXPIRATIONDATE)) then
                MLR2.STATUSCODE  -- When the current date is within the second rules period

            when (MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID is not null and MLR3.INTERVALCODE = 12 or @ASOF <= dateadd(month, (MLR3.INTERVALCODE+1 + MLR1.INTERVALCODE+1 + MLR2.INTERVALCODE+1), MEMBERSHIPTRANSACTION.EXPIRATIONDATE)) then
                MLR3.STATUSCODE  -- When the current date is within the third rules period

            else
                4  -- Lapsed

        end as STATUSCODE
    from
        dbo.MEMBERSHIPTRANSACTION
    inner join
        dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
    inner join
        dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
    inner join
        dbo.MEMBERSHIP ON MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
    left join
        dbo.MEMBERSHIPLEVELRENEWAL MLR1 on MLR1.ID = MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID
    left join
        dbo.MEMBERSHIPLEVELRENEWAL MLR2 on MLR2.ID = MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID
    left join
        dbo.MEMBERSHIPLEVELRENEWAL MLR3 on MLR3.ID = MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID
    where
        MEMBERSHIPTRANSACTION.ID = @ID
)