UFN_MEMBERSHIPPROGRAM_ACTIVITYBREAKDOWN

CREATE function dbo.UFN_MEMBERSHIPPROGRAM_ACTIVITYBREAKDOWN
(
    @PROGRAMID uniqueidentifier,
    @ACTIONCODE tinyint = null,
    @FROMDATE datetime = null,
    @TODATE datetime = null
)
returns @MEMBERSHIPBREAKDOWN table
(
    MEMBERSHIPID uniqueidentifier,
    EXPIRATIONDATE date,
    MEMBERSHIPLEVELID uniqueidentifier,
    TIERCODEID uniqueidentifier,
    TRANSACTIONDATE datetime,
    TRANSACTIONID uniqueidentifier
)
with execute as caller
as begin

    set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

    -- Handle lapsed differently since the calculation is not based on action code

    if @ACTIONCODE = 100
    begin
        insert into @MEMBERSHIPBREAKDOWN
        select distinct
            MEMBERSHIPTRANSACTION.MEMBERSHIPID,
            (
                select top(1) 
                    EXPIRATIONDATE
                from dbo.MEMBERSHIPTRANSACTION MT
                where MT.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                    and MT.EXPIRATIONDATE between @FROMDATE and @TODATE
                    and MT.MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                order by TRANSACTIONDATE desc
            ),
            MEMBERSHIPLEVEL.ID,
            MEMBERSHIPLEVEL.TIERCODEID,
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
            MEMBERSHIPTRANSACTION.ID
        from dbo.MEMBERSHIPTRANSACTION
        inner join dbo.MEMBERSHIP
            on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
        inner join dbo.MEMBERSHIPLEVEL
            on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
        where MEMBERSHIPTRANSACTION.EXPIRATIONDATE between @FROMDATE and @TODATE
            and MEMBERSHIPTRANSACTION.ACTIONCODE <> 4 -- Dropped are counted elsewhere

            and not exists (
                select
                    1
                from dbo.MEMBERSHIPTRANSACTION MT
                where MT.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                    and MT.TRANSACTIONDATE between MEMBERSHIPTRANSACTION.TRANSACTIONDATE and @TODATE
                    and MT.DATEADDED > MEMBERSHIPTRANSACTION.DATEADDED
            )
            and MEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
            and MEMBERSHIP.STATUSCODE <> 2

    end

    -- Handle the 'All active memberships' case

    if @ACTIONCODE = 50
    begin
        insert into @MEMBERSHIPBREAKDOWN
        select
            MEMBERSHIP.ID,
            MEMBERSHIP.EXPIRATIONDATE,
            MEMBERSHIPLEVEL.ID,
            MEMBERSHIPLEVEL.TIERCODEID,
            null,
            null
        from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPLEVEL
            on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
        where MEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
            and STATUSCODE = 0
    end

    -- Handle renew/rejoin together using the renewal action code

    if @ACTIONCODE = 1
    begin
        insert into @MEMBERSHIPBREAKDOWN
        select distinct
            MEMBERSHIPTRANSACTION.MEMBERSHIPID,
            (
                select top(1) 
                    EXPIRATIONDATE 
                from dbo.MEMBERSHIPTRANSACTION MT
                where MT.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                    and MT.TRANSACTIONDATE between @FROMDATE and @TODATE
                    and MT.MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                order by TRANSACTIONDATE desc
            ),
            MEMBERSHIPLEVEL.ID,
            MEMBERSHIPLEVEL.TIERCODEID,
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
            MEMBERSHIPTRANSACTION.ID
        from dbo.MEMBERSHIPTRANSACTION
        inner join dbo.MEMBERSHIP
            on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
        inner join dbo.MEMBERSHIPLEVEL
            on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
        where MEMBERSHIPTRANSACTION.ACTIONCODE in (1,5)
            and TRANSACTIONDATE between @FROMDATE and @TODATE
            and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
            and MEMBERSHIP.STATUSCODE <> 2
    end

    if @ACTIONCODE not in (1,50,100)
    begin
        insert into @MEMBERSHIPBREAKDOWN
        select distinct
            MEMBERSHIPTRANSACTION.MEMBERSHIPID,
            (
                select top(1) 
                    EXPIRATIONDATE 
                from dbo.MEMBERSHIPTRANSACTION MT
                where MT.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                    and MT.TRANSACTIONDATE between @FROMDATE and @TODATE
                    and MT.MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
                order by TRANSACTIONDATE desc
            ),
            MEMBERSHIPLEVEL.ID,
            MEMBERSHIPLEVEL.TIERCODEID,
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
            MEMBERSHIPTRANSACTION.ID
        from dbo.MEMBERSHIPTRANSACTION
        inner join dbo.MEMBERSHIP
            on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
        inner join dbo.MEMBERSHIPLEVEL
            on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
        where MEMBERSHIPTRANSACTION.ACTIONCODE = @ACTIONCODE
            and MEMBERSHIPTRANSACTION.TRANSACTIONDATE between @FROMDATE and @TODATE
            and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
            and MEMBERSHIP.STATUSCODE <> 2
    end

    return
end