UFN_MEMBERSHIPPROGRAM_GETLONGESTTERMLENGTH_INMONTHS

create function dbo.UFN_MEMBERSHIPPROGRAM_GETLONGESTTERMLENGTH_INMONTHS (
    @MEMBERSHIPPROGRAMID uniqueidentifier
)
returns table
as
    return 
        --Determine longest contribution-based term in months

        select max(TERM.LENGTH) as LENGTH
        from dbo.MEMBERSHIPPROGRAM
        inner join dbo.MEMBERSHIPLEVEL ML on ML.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
        inner join dbo.MEMBERSHIPLEVELTERM MLT on MLT.LEVELID = ML.ID
        cross apply (
            select
                case TERMLENGTHCODE
                    when 0 then TERMTIMELENGTH --Monthly

                    when 1 then TERMTIMELENGTH * 12 --Yearly

                end as LENGTH
        ) as TERM
        where MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID