USP_DATALIST_MEMBERSHIPCOUNTREPORT

-- NOTE: Logic was pulled from UFN_MEMBERSHIPTRANSACTION_GETMEMBERSHIPCOUNT_BYASOFDATE. Any changes

-- here should be made there as well.

CREATE procedure dbo.USP_DATALIST_MEMBERSHIPCOUNTREPORT
(
    @DATE tinyint,
    @DATEFROM datetime,
    @DATETO datetime,
    @PROGRAMID uniqueidentifier,
    @BY tinyint,
    @GROUPBY tinyint
)
as
    set nocount on;
    declare @TODAY datetime = getdate();

    if object_id('tempdb..#PERIODTABLE') is not null
        drop table #PERIODTABLE

    create table #PERIODTABLE
    (  
        PERIOD nvarchar(26) collate DATABASE_DEFAULT,  
        PERIODSTARTDATE datetime,  
        PERIODENDDATE datetime
    );  

    --create a data table with all possible period's for a given range (@DATEFROM - @DATETO)


    with  datetable as(
        select  DateValue = @DATEFROM
        union all
        select DateValue + 1
        from    datetable 
        where   DateValue + 1 < @DATETO 
    )    
    INSERT INTO #PERIODTABLE(PERIOD, PERIODSTARTDATE, PERIODENDDATE) 

    --these case statements determine how the date range table will be grouped; by month, quarter, or year


    select distinct 
    case @BY
        when 0 then convert(nvarchar(2),datepart(month,DateValue)) + ' / ' + convert(nvarchar(4),datepart(year,DateValue))
        when 1 then 'Q' + convert(nvarchar(2),datepart(quarter,DateValue))+ ' - '+convert(nvarchar(4),datepart(year,DateValue)) 
        when 2 then convert(nvarchar(4),datepart(year,DateValue))
    end as PERIOD,
    case @BY
        when 0 then dbo.UFN_DATE_THISMONTH_FIRSTDAY(DateValue,0)
        when 1 then dbo.UFN_DATE_THISQUARTER_FIRSTDAY(DateValue,0)
        when 2 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(DateValue,0)
    end as PERIODSTARTDATE,
    case @BY
        when 0 then dbo.UFN_DATE_THISMONTH_LASTDAY(DateValue,0)
        when 1 then dbo.UFN_DATE_THISQUARTER_LASTDAY(DateValue,0)
        when 2 then dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(DateValue,0)
    end    as PERIODENDDATE    
    from datetable
    OPTION (MAXRECURSION 0);

    --check to make sure nothing outside the given date range is used


    update #PERIODTABLE
    set PERIODSTARTDATE = @DATEFROM
    where PERIODSTARTDATE < @DATEFROM;

    update #PERIODTABLE
    set PERIODENDDATE = @DATETO
    where PERIODENDDATE > @DATETO;

    -- Push transactions into a temp table so UFN_MEMBERSHIPTRANSACTION_GETMEMBERSHIPCOUNT_BYASOFDATE doesn't have to be used

    if object_id('tempdb..#MEMBERSHIPBYDATE') is not null
        drop table #MEMBERSHIPBYDATE

    create table #MEMBERSHIPBYDATE
    (
        MEMBERSHIPTRANSACTIONID uniqueidentifier,
        TRANSACTIONDATE datetime,
        EXPIRATIONDATE datetime,
        MEMBERSHIPLEVELTERMID uniqueidentifier,
        MEMBERSHIPLEVELID uniqueidentifier,
        MEMBERSHIPLEVELTYPECODEID uniqueidentifier,
        MEMBERSHIPID uniqueidentifier,
        PERIODENDDATE datetime
    )

    insert into #MEMBERSHIPBYDATE
    (
        MEMBERSHIPTRANSACTIONID, 
        TRANSACTIONDATE, 
        EXPIRATIONDATE, 
        MEMBERSHIPLEVELTERMID, 
        MEMBERSHIPLEVELID, 
        MEMBERSHIPLEVELTYPECODEID, 
        MEMBERSHIPID,
        PERIODENDDATE
    )
    select
        MT.ID, 
        MT.TRANSACTIONDATE, 
        MT.EXPIRATIONDATE, 
        MT.MEMBERSHIPLEVELTERMID, 
        MT.MEMBERSHIPLEVELID, 
        MT.MEMBERSHIPLEVELTYPECODEID, 
        MT.MEMBERSHIPID,
        PERIODTABLE.PERIODENDDATE
    from #PERIODTABLE as PERIODTABLE
    cross apply
    (
        select
            MT.ID, 
            MT.TRANSACTIONDATE, 
            MT.EXPIRATIONDATE, 
            MT.MEMBERSHIPLEVELTERMID, 
            MT.MEMBERSHIPLEVELID, 
            MT.MEMBERSHIPLEVELTYPECODEID, 
            MT.MEMBERSHIPID
        from dbo.UFN_MEMBERSHIP_LATESTTRANSACTIONASOF_BULK(PERIODTABLE.PERIODENDDATE) MT
        inner join dbo.UFN_MEMBERSHIP_WITHRENEWALWINDOW_GETNOLONGERACTIVEDATES() ACTIVEWINDOWENDDATES on MT.ID = ACTIVEWINDOWENDDATES.MEMBERSHIPTRANSACTIONID
        inner join dbo.MEMBERSHIPLEVELTERM MLT on MLT.ID = MT.MEMBERSHIPLEVELTERMID
        inner join dbo.MEMBERSHIP M on M.ID = MT.MEMBERSHIPID
        inner join dbo.MEMBERSHIPPROGRAM MP on MP.ID = M.MEMBERSHIPPROGRAMID
        where MP.ID = @PROGRAMID
            and (
                ACTIVEWINDOWENDDATES.NOLONGERACTIVEDATE >= PERIODTABLE.PERIODENDDATE
                or MLT.TERMCODE = 6
                or MP.PROGRAMTYPECODE in (1, 2) --Recurring/Sustaining and Lifetime

            )
            and MT.ACTIONCODE <> 4
    ) as MT;

    with MEMBERSHIPLEVELBYPERIOD_CTE as
    (
        select
            PERIODTABLE.PERIOD,
            PERIODTABLE.PERIODENDDATE,
            MEMBERSHIPLEVEL.ID as MEMBERSHIPLEVELID,
            MEMBERSHIPLEVEL.NAME as MEMBERSHIPLEVELNAME
        from dbo.MEMBERSHIPLEVEL
        cross join #PERIODTABLE as PERIODTABLE
        where
            MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
    )
    select [PERIOD], [COUNT], [GROUPEDBYCOLUMN], [PERIODENDDATE] 
    from 
    (
        select
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIOD,  
            (
                select 
                    count(*)
                from #MEMBERSHIPBYDATE MEMBERSHIPBYDATE
                where 
                    MEMBERSHIPBYDATE.PERIODENDDATE = MEMBERSHIPLEVELBYPERIOD_CTE.PERIODENDDATE and
                    MEMBERSHIPBYDATE.MEMBERSHIPLEVELID = MEMBERSHIPLEVELBYPERIOD_CTE.MEMBERSHIPLEVELID
            ) as [COUNT],
            MEMBERSHIPLEVELBYPERIOD_CTE.MEMBERSHIPLEVELNAME as GROUPEDBYCOLUMN,
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIODENDDATE
        from MEMBERSHIPLEVELBYPERIOD_CTE
        where
            @GROUPBY = 1
        group by      
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIODENDDATE,
            MEMBERSHIPLEVELBYPERIOD_CTE.MEMBERSHIPLEVELID,
            MEMBERSHIPLEVELBYPERIOD_CTE.MEMBERSHIPLEVELNAME,
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIOD

        union all   

        select   
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIOD,  
            (
                select 
                    count(*)
                from #MEMBERSHIPBYDATE MEMBERSHIPBYDATE
                where MEMBERSHIPBYDATE.PERIODENDDATE = MEMBERSHIPLEVELBYPERIOD_CTE.PERIODENDDATE
                    and (MEMBERSHIPBYDATE.MEMBERSHIPLEVELTYPECODEID = MEMBERSHIPLEVELTYPECODE.ID
                        or (MEMBERSHIPBYDATE.MEMBERSHIPLEVELTYPECODEID is null and MEMBERSHIPLEVELTYPECODE.ID is null)
                    )
            ) as [COUNT],
            coalesce(MEMBERSHIPLEVELTYPECODE.DESCRIPTION, 'No Type') as GROUPEDBYCOLUMN,
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIODENDDATE
        from MEMBERSHIPLEVELBYPERIOD_CTE
        left join dbo.MEMBERSHIPLEVELTYPE on MEMBERSHIPLEVELBYPERIOD_CTE.MEMBERSHIPLEVELID = MEMBERSHIPLEVELTYPE.LEVELID
        left join dbo.MEMBERSHIPLEVELTYPECODE on MEMBERSHIPLEVELTYPE.LEVELTYPECODEID = MEMBERSHIPLEVELTYPECODE.ID
        where
            @GROUPBY = 0
        group by
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIODENDDATE,
            MEMBERSHIPLEVELTYPECODE.ID,
            MEMBERSHIPLEVELTYPECODE.DESCRIPTION,
            MEMBERSHIPLEVELBYPERIOD_CTE.PERIOD
    ) as a
    where a.COUNT > 0
    order by
        A.PERIODENDDATE,
        a.GROUPEDBYCOLUMN;