UFN_MEMBERSHIP_GETLEVELBYDATE

Returns the membership level for the given time.

Return

Return Type
uniqueidentifier

Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPID	uniqueidentifier	IN
@DATE	datetime	IN

Definition

Copy



            CREATE function dbo.UFN_MEMBERSHIP_GETLEVELBYDATE
            (
                @MEMBERSHIPID uniqueidentifier,
                @DATE datetime = null
            )
            returns uniqueidentifier
            as begin
                declare @MEMBERSHIPLEVELID uniqueidentifier = null;

                select top (1) @MEMBERSHIPLEVELID = 
                    case 
                        when [ACTIONCODE] = 4 then --If the latest transaction was dropped

                            null
                        when [MEMBERSHIPTRANSACTION].[EXPIRATIONDATE] <= @DATE then  --If the latest transaction had expired

                            null
                        else
                            [MEMBERSHIPLEVELID]
                    end
                from dbo.[MEMBERSHIPTRANSACTION]
                where [MEMBERSHIPTRANSACTION].[TRANSACTIONDATE] <= @DATE
                and [MEMBERSHIPTRANSACTION].[MEMBERSHIPID] = @MEMBERSHIPID
                order by [MEMBERSHIPTRANSACTION].[TRANSACTIONDATE] desc;

                return @MEMBERSHIPLEVELID;
            end