UFN_MEMBERSHIPLEVELS_GETRETENTIONRATE

CREATE function [dbo].[UFN_MEMBERSHIPLEVELS_GETRETENTIONRATE](@MEMBERSHIPPROGRAMID uniqueidentifier, @LASTENDDATE datetime, @THISENDDATE datetime)
returns table
return
(
    with PREVIOUSMEMBERS_CTE as
    (
        select 
            MEMBERSHIPID, MEMBERSHIPLEVELID
        from 
            dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @LASTENDDATE) 
        where 
            EXPIRATIONDATE < @THISENDDATE -- exclude memberships that didn't need to renew 

            and ACTIONCODE <> 4
    ),
    RETAINEDMEMBERS_CTE as
    (
        select 
            MEMBERSHIPID
        from 
            dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@MEMBERSHIPPROGRAMID, @THISENDDATE)
        where 
            ACTIONCODE <> 4
    )
    select 
        PREV.MEMBERSHIPLEVELID,
        case COUNT(distinct PREV.MEMBERSHIPID)
            when 0 then 0
            else
            (
                convert(decimal(13,4), COUNT(distinct RET.MEMBERSHIPID)) / convert(decimal(13,4), COUNT(distinct PREV.MEMBERSHIPID))
            )
        end RETENTIONRATE
    from 
        PREVIOUSMEMBERS_CTE PREV
        left join RETAINEDMEMBERS_CTE RET on PREV.MEMBERSHIPID = RET.MEMBERSHIPID 
    group by 
        PREV.MEMBERSHIPLEVELID
)