USP_DATALIST_MEMBERSHIPRENEWALREPORT

CREATE procedure dbo.USP_DATALIST_MEMBERSHIPRENEWALREPORT
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @MEMBERSHIPPROGRAMID uniqueidentifier
)
as
    set nocount on;

-- Validate the date range


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

-- Remove dates outside all data


declare @CURRENTDATE datetime = getdate();

declare @COMPAREDATE datetime;
select @COMPAREDATE = MIN(TRANSACTIONDATE) from dbo.MEMBERSHIPTRANSACTION;

if @STARTDATE < @COMPAREDATE
    set @STARTDATE = @COMPAREDATE;

select @COMPAREDATE = MAX(TRANSACTIONDATE) from dbo.MEMBERSHIPTRANSACTION;

if @ENDDATE > @COMPAREDATE
    set @ENDDATE = @COMPAREDATE;

-- Make sure this is the absolute earliest/latest time

-- to prevent bugs at the edge of the date range


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

-- Find what happened

declare @WHATHAPPENEDTABLE as table(
    TRANSACTIONDATE datetime,
    EXPIRATIONDATE datetime,
    THISACTIONCODE tinyint,
    NEXTACTIONCODE tinyint,
    MEMBERSHIPID uniqueidentifier
);
insert into @WHATHAPPENEDTABLE (
    TRANSACTIONDATE,
    EXPIRATIONDATE,
    THISACTIONCODE,
    NEXTACTIONCODE,
    MEMBERSHIPID
) select
    MT1.TRANSACTIONDATE,
    MT1.EXPIRATIONDATE,
    MT1.ACTIONCODE as THISACTIONCODE,
    (
        select top(1)
            MT2.ACTIONCODE
        from
            MEMBERSHIPTRANSACTION as MT2
        where
            MT2.MEMBERSHIPID = MT1.MEMBERSHIPID and
            (
                MT2.TRANSACTIONDATE > MT1.TRANSACTIONDATE or 
                MT2.EXPIRATIONDATE > MT1.EXPIRATIONDATE
            ) and
            MT2.TRANSACTIONDATE between @STARTDATE and @ENDDATE
        order by
            MT2.TRANSACTIONDATE asc
    ) as NEXTACTIONCODE,
    MT1.MEMBERSHIPID
from 
    dbo.MEMBERSHIPTRANSACTION as MT1
    inner join dbo.MEMBERSHIP on MT1.MEMBERSHIPID = MEMBERSHIP.ID
where
    MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
    MT1.TRANSACTIONDATE between @STARTDATE and @ENDDATE
  and MEMBERSHIP.STATUSCODE <> 2;


declare @MONTHTABLE table(
    FIRSTDAYOFTHEMONTH datetime,
    LASTDAYOFTHEMONTH datetime)

declare @TEMPDATE datetime = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@STARTDATE, 0)
while @TEMPDATE <= @ENDDATE
begin
    declare @LASTDAYOFTHEMONTH datetime = dbo.UFN_DATE_THISMONTH_LASTDAY(@TEMPDATE, 1)
    insert into @MONTHTABLE values (@TEMPDATE, @LASTDAYOFTHEMONTH)
    set @TEMPDATE = dateadd(m, 1, @TEMPDATE)
end

select
    cast(month(MT.FIRSTDAYOFTHEMONTH) as nvarchar(2)) + '/' + cast(year(MT.FIRSTDAYOFTHEMONTH) as nvarchar(4)) as EXPIRATIONMONTH,
    (select count(MEMBERSHIP.ID) 
        from dbo.MEMBERSHIPTRANSACTION
            inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
        where MEMBERSHIP.EXPIRATIONDATE between MT.FIRSTDAYOFTHEMONTH and MT.LASTDAYOFTHEMONTH and
        MEMBERSHIP.EXPIRATIONDATE >= @CURRENTDATE and
        MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
    ) as NUMEXPIRING,
    (select COUNT(MT2.MEMBERSHIPID)
        from @WHATHAPPENEDTABLE as MT2
        where 
            MT2.THISACTIONCODE in (1,5) and
            MT2.TRANSACTIONDATE between MT.FIRSTDAYOFTHEMONTH and MT.LASTDAYOFTHEMONTH
    ) as NUMRENEWED,
    (select COUNT(MT2.MEMBERSHIPID)
        from @WHATHAPPENEDTABLE as MT2
        where 
            MT2.THISACTIONCODE = 2 and
            MT2.TRANSACTIONDATE between MT.FIRSTDAYOFTHEMONTH and MT.LASTDAYOFTHEMONTH
    ) as NUMUPGRADED,
    (select COUNT(MT2.MEMBERSHIPID)
        from @WHATHAPPENEDTABLE as MT2
        where 
            MT2.THISACTIONCODE = 3 and
            MT2.TRANSACTIONDATE between MT.FIRSTDAYOFTHEMONTH and MT.LASTDAYOFTHEMONTH
    ) as NUMDOWNGRADED,
    (select COUNT(MT2.MEMBERSHIPID)
        from @WHATHAPPENEDTABLE as MT2
        where 
            MT2.THISACTIONCODE = 4 and
            MT2.TRANSACTIONDATE between MT.FIRSTDAYOFTHEMONTH and MT.LASTDAYOFTHEMONTH
    ) as NUMDROPPED,
    (select count(*)
        from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
        where 
            MEMBERSHIP.EXPIRATIONDATE between MT.FIRSTDAYOFTHEMONTH and MT.LASTDAYOFTHEMONTH and
            MEMBERSHIP.EXPIRATIONDATE < @CURRENTDATE and
            MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID and
            MEMBERSHIPTRANSACTION.ACTIONCODE <> 4 AND
            (MEMBERSHIPTRANSACTION.EXPIRATIONDATE between @STARTDATE and @ENDDATE) and
            not exists (
                select
                    1
                from dbo.MEMBERSHIPTRANSACTION MT
                where MT.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                    and MT.TRANSACTIONDATE between MEMBERSHIPTRANSACTION.TRANSACTIONDATE and @ENDDATE
                    and MT.DATEADDED > MEMBERSHIPTRANSACTION.DATEADDED
            )
    ) as NUMLAPSED
from @MONTHTABLE as MT 
order by MT.FIRSTDAYOFTHEMONTH 

return 0;