UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF

CREATE function dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF
(
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @ASOF date
)
returns table
as
return
(
    select
        LATESTTRANSACTION.MEMBERSHIPID,
        LATESTTRANSACTION.MEMBERSHIPLEVELID,
        LATESTTRANSACTION.ACTIONCODE,
        LATESTTRANSACTION.EXPIRATIONDATE
    from
        dbo.MEMBERSHIP
    cross apply (
        select top 1
            MEMBERSHIPTRANSACTION.MEMBERSHIPID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            MEMBERSHIPTRANSACTION.ACTIONCODE,
            MEMBERSHIPTRANSACTION.EXPIRATIONDATE
        from
            dbo.MEMBERSHIPTRANSACTION
        where
            MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
            and convert(date, TRANSACTIONDATE) <= @ASOF
        order by
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE desc,
            MEMBERSHIPTRANSACTION.DATEADDED desc
    ) as LATESTTRANSACTION
    where
        MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        -- This function was originally written to filter out expired memberships as of the given date

        -- so this is added to keep the functionality consistent with the way it used to work

        -- though we may one day let the caller filter out expired.

        and (LATESTTRANSACTION.EXPIRATIONDATE is null or (dbo.UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETNOLONGERACTIVEDATE(MEMBERSHIP.ID, LATESTTRANSACTION.EXPIRATIONDATE) >= @ASOF))
        and MEMBERSHIP.STATUSCODE <> 2
)