USP_DATALIST_MEMBERSHIPCONVERSIONREPORT

CREATE procedure dbo.USP_DATALIST_MEMBERSHIPCONVERSIONREPORT
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null,
    @GROUPBYTIER bit = 0,
    @HIDECOLUMNSROWSWITHNODATA bit = 0
)
as
    set nocount on;

if (@STARTDATE is null) or (@ENDDATE is null) begin
    raiserror('Invalid date range.', 13, 1);
    return 1;
end

-- Prevent lapsing people in the future

if @ENDDATE > getdate() set @ENDDATE = getdate();

-- Make sure we have the largest window for the given dates

set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);


declare @TRANSACTIONS as table (
    MEMBERSHIPID uniqueidentifier,
    BEGINNINGLEVELID uniqueidentifier,
    BEGINNINGISACTIVE bit,
    ENDINGLEVELID uniqueidentifier,
    ENDINGACTIONCODE tinyint,
    ISDECEASED bit
);

-- Get memberships as of start date and memberships as of end date, then group them according to the net change.

-- - BEGINNING is the most recent transaction *before the interval*, regardless of how recent or what kind of transaction.

-- - ENDING is the latest transaction that occurred *during the interval* for each membership.

-- - ENDING has one additional limitation: either its expiration date must be after the end of the interval or its action must be 'drop'.

-- - - This removes transactions that lapse before the interval ends but keeps explicit 'drop' transactions for previously lapsed memberships (ultimate result should still be 'Not renewed' for both).

with BEGINNING as
(
    select MT.MEMBERSHIPID,
        MT.MEMBERSHIPLEVELID,
        MT.EXPIRATIONDATE,
        TRANSACTIONEXPIRATIONDATES.NOLONGERACTIVEDATE,
        MT.ACTIONCODE
    from dbo.UFN_MEMBERSHIP_LATESTTRANSACTIONASOF_BULK(dbo.UFN_DATE_GETLATESTTIME(@STARTDATE - 1)) MT
    inner join dbo.UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETNOLONGERACTIVEDATES() as TRANSACTIONEXPIRATIONDATES
        on MT.ID = TRANSACTIONEXPIRATIONDATES.MEMBERSHIPTRANSACTIONID
    inner join dbo.MEMBERSHIPLEVEL on MT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
),
ENDING as
(
    select MEMBERSHIPID,
        MEMBERSHIPLEVELID,
        ACTIONCODE
    from dbo.UFN_MEMBERSHIP_LATESTTRANSACTIONASOF_BULK(@ENDDATE) MT
    inner join dbo.MEMBERSHIPLEVEL on MT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
        and TRANSACTIONDATE >= @STARTDATE
        and (EXPIRATIONDATE > @ENDDATE or EXPIRATIONDATE is null or ACTIONCODE = 4)
)
insert into @TRANSACTIONS
select
    coalesce(BEGINNING.MEMBERSHIPID, ENDING.MEMBERSHIPID),
    BEGINNING.MEMBERSHIPLEVELID,
    case
        when (BEGINNING.MEMBERSHIPID is null or BEGINNING.NOLONGERACTIVEDATE < @STARTDATE or BEGINNING.ACTIONCODE = 4) then 0
        else 1
    end as BEGINNINGISACTIVE,
    ENDING.MEMBERSHIPLEVELID,
    ENDING.ACTIONCODE,
    0 -- ISDECEASED is set below

from BEGINNING full join ENDING
    on BEGINNING.MEMBERSHIPID = ENDING.MEMBERSHIPID
where BEGINNING.EXPIRATIONDATE between @STARTDATE and @ENDDATE
    or ENDING.MEMBERSHIPID is not null        --Limit output to only the memberships that have changed during the time period (either a lapse or a new transaction has occurred)


-- Get all memberships with no living members and set 'deceased' accordingly

-- I'm not sure this implementation is correct, because it does not take date of death into account.

update @TRANSACTIONS
set ISDECEASED = 1
where
    MEMBERSHIPID not in (
        select MEMBER.MEMBERSHIPID
        from dbo.MEMBER
            inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
            left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = MEMBER.CONSTITUENTID
        where MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
            and DECEASEDCONSTITUENT.ID is null
    )

declare @RETURNTABLE table(
    STARTLEVELNAME nvarchar(255),
    STARTLEVELRANK int,
    ENDLEVELNAME nvarchar(255),
    ENDLEVELRANK int,
    MOVEMENTCOUNT bigint,
    STARTLEVELACTIVE int
)

insert into @RETURNTABLE
select
    case @GROUPBYTIER
        when 0 then MLSTART.NAME
        when 1 then coalesce(TIERSTART.DESCRIPTION, '(no reporting group)')
    end as STARTLEVELNAME,
    case @GROUPBYTIER
        when 0 then (MLSTART.SEQUENCE + 2)
        when 1 then coalesce((TIERSTART.SEQUENCE + 3), 2)
    end as STARTLEVELRANK,
    case @GROUPBYTIER
        when 0 then MLEND.NAME
        when 1 then coalesce(TIEREND.DESCRIPTION, '(no reporting group)')
    end as ENDLEVELNAME,
    case @GROUPBYTIER
        when 0 then (MLEND.SEQUENCE + 2)
        when 1 then coalesce((TIEREND.SEQUENCE + 3), 2)
    end as ENDLEVELRANK,
    (
        select count(MEMBERSHIPID)
        from @TRANSACTIONS
        where BEGINNINGLEVELID = MLSTART.ID
            and BEGINNINGISACTIVE = 1        -- Don't include any that were dropped before or during the interval; those go under Rejoin or Not Renewed.

            and ENDINGLEVELID = MLEND.ID
            and ENDINGACTIONCODE <> 4
    ) as MOVEMENTCOUNT,
    0 as STARTLEVELACTIVE
from
    dbo.MEMBERSHIPLEVEL as MLSTART
    inner join dbo.MEMBERSHIPLEVEL as MLEND on MLSTART.MEMBERSHIPPROGRAMID = MLEND.MEMBERSHIPPROGRAMID
    left join dbo.TIERCODE TIERSTART on MLSTART.TIERCODEID = TIERSTART.ID
    left join dbo.TIERCODE TIEREND on MLEND.TIERCODEID = TIEREND.ID
where
    MLSTART.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
    and MLEND.ISACTIVE = 1

union all

-- Not renewed memberships (lapsed and cancelled, including cancellations that had previously lapsed)

select
    case @GROUPBYTIER
        when 0 then MLSTART.NAME
        when 1 then coalesce(TIERSTART.DESCRIPTION, '(no reporting group)')
    end as STARTLEVELNAME,
    case @GROUPBYTIER
        when 0 then (MLSTART.SEQUENCE + 2)
        when 1 then coalesce((TIERSTART.SEQUENCE + 3), 2)
    end as STARTLEVELRANK,
    'Not renewed' as ENDLEVELNAME,
    0 as ENDLEVELRANK,
    (
        select count(MEMBERSHIPID)
        from @TRANSACTIONS T
        where BEGINNINGLEVELID = MLSTART.ID
            and ((BEGINNINGISACTIVE = 1 and ENDINGLEVELID is null)
                or ENDINGACTIONCODE = 4)
            and ISDECEASED = 0
    ) as MOVEMENTCOUNT,
    MLSTART.ISACTIVE as STARTLEVELACTIVE --throw a flag if the level is active

from
    dbo.MEMBERSHIPLEVEL as MLSTART
    left join dbo.TIERCODE TIERSTART on MLSTART.TIERCODEID = TIERSTART.ID
where
    MLSTART.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID

union all

-- Deceased memberships (those that would have been 'Not renewed' if they hadn't died instead)

select
    case @GROUPBYTIER
        when 0 then MLSTART.NAME
        when 1 then coalesce(TIERSTART.DESCRIPTION, '(no reporting group)')
    end as STARTLEVELNAME,
    case @GROUPBYTIER
        when 0 then (MLSTART.SEQUENCE + 2)
        when 1 then coalesce((TIERSTART.SEQUENCE + 3), 2)
    end as STARTLEVELRANK,
    'Deceased' as ENDLEVELNAME,
    1 as ENDLEVELRANK,
    (
        select count(MEMBERSHIPID)
        from @TRANSACTIONS TNS
        where BEGINNINGLEVELID = MLSTART.ID
            and ((BEGINNINGISACTIVE = 1 and ENDINGLEVELID is null)
                or ENDINGACTIONCODE = 4)
            and ISDECEASED = 1
    ) as MOVEMENTCOUNT,
    0 as STARTLEVELACTIVE
from
    dbo.MEMBERSHIPLEVEL as MLSTART
    left join dbo.TIERCODE TIERSTART on MLSTART.TIERCODEID = TIERSTART.ID
where
    MLSTART.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID

union all

select
    'New' as STARTLEVELNAME,
    0 as STARTLEVELRANK,
    case @GROUPBYTIER
        when 0 then MLEND.NAME
        when 1 then coalesce(TIEREND.DESCRIPTION, '(no reporting group)')
    end as ENDLEVELNAME,
    case @GROUPBYTIER
        when 0 then (MLEND.SEQUENCE + 2)
        when 1 then coalesce((TIEREND.SEQUENCE + 3), 2)
    end as ENDLEVELRANK,
    (
        select count(MEMBERSHIPID)
        from @TRANSACTIONS
        where BEGINNINGLEVELID is null
            and ENDINGLEVELID = MLEND.ID
            and ENDINGACTIONCODE <> 4
    ) as MOVEMENTCOUNT,
    1 as STARTLEVELACTIVE
from
    dbo.MEMBERSHIPLEVEL as MLEND
    left join dbo.TIERCODE TIEREND on MLEND.TIERCODEID = TIEREND.ID
where
    MLEND.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
    MLEND.ISACTIVE = 1

union all

-- Rejoins (includes rejoin transactions whose level differs from the previous transaction's level)

select
    'Rejoin' as STARTLEVELNAME,
    1 as STARTLEVELRANK,
    case @GROUPBYTIER
        when 0 then MLEND.NAME
        when 1 then coalesce(TIEREND.DESCRIPTION, '(no reporting group)')
    end as ENDLEVELNAME,
    case @GROUPBYTIER
        when 0 then (MLEND.SEQUENCE + 2)
        when 1 then coalesce((TIEREND.SEQUENCE + 3), 2)
    end as ENDLEVELRANK,
    (
        select count(MEMBERSHIPID)
        from @TRANSACTIONS
        where BEGINNINGLEVELID is not null
            and BEGINNINGISACTIVE = 0
            and ENDINGLEVELID = MLEND.ID
            and ENDINGACTIONCODE <> 4
    ) as MOVEMENTCOUNT,
    1 as STARTLEVELACTIVE
from
    dbo.MEMBERSHIPLEVEL as MLEND
    left join dbo.TIERCODE TIEREND on MLEND.TIERCODEID = TIEREND.ID
where
    MLEND.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
    and MLEND.ISACTIVE = 1

union all

select
    'New' as STARTLEVELNAME,
    0 as STARTLEVELRANK,
    'Not renewed' as ENDLEVELNAME,
    0 as ENDLEVELRANK,
    0 as MOVEMENTCOUNT,
    1 as STARTLEVELACTIVE

union all

select
    'New' as STARTLEVELNAME,
    0 as STARTLEVELRANK,
    'Deceased' as ENDLEVELNAME,
    1 as ENDLEVELRANK,
    0 as MOVEMENTCOUNT,
    0 as STARTLEVELACTIVE

union all

select
    'Rejoin' as STARTLEVELNAME,
    1 as STARTLEVELRANK,
    'Not renewed' as ENDLEVELNAME,
    0 as ENDLEVELRANK,
    0 as MOVEMENTCOUNT,
    1 as STARTLEVELACTIVE

union all

select
    'Rejoin' as STARTLEVELNAME,
    1 as STARTLEVELRANK,
    'Deceased' as ENDLEVELNAME,
    1 as ENDLEVELRANK,
    0 as MOVEMENTCOUNT,
    0 as STARTLEVELACTIVE

order by
    STARTLEVELRANK,
    ENDLEVELRANK

if @HIDECOLUMNSROWSWITHNODATA = 1
begin

    -- Hide the columns that don't have any value

    delete @RETURNTABLE 
    from @RETURNTABLE RETURNTABLE 
    inner join (
        select ENDLEVELRANK from @RETURNTABLE 
        group by ENDLEVELRANK
        having SUM(MOVEMENTCOUNT) = 0 and ENDLEVELRANK > 1) as GROUPTABLE 
    on RETURNTABLE.ENDLEVELRANK = GROUPTABLE.ENDLEVELRANK 

    -- Hide the rows that don't have any value

    delete @RETURNTABLE 
    from @RETURNTABLE RETURNTABLE 
    inner join (
        select STARTLEVELRANK from @RETURNTABLE 
        group by STARTLEVELRANK
        having SUM(MOVEMENTCOUNT) = 0 and STARTLEVELRANK > 1) as GROUPTABLE 
    on RETURNTABLE.STARTLEVELRANK = GROUPTABLE.STARTLEVELRANK 
end

select STARTLEVELNAME,
    STARTLEVELRANK,
    ENDLEVELNAME,
    ENDLEVELRANK,
    MOVEMENTCOUNT,
    STARTLEVELACTIVE
from @RETURNTABLE 
order by
    STARTLEVELRANK,
    ENDLEVELRANK

return 0;