UFN_FEPSUBMITPROCESS_GETDATA

CREATE function dbo.UFN_FEPSUBMITPROCESS_GETDATA
(
    @TYPE tinyint,
    @PERIOD1STARTDATE datetime,
    @PERIOD1ENDDATE datetime,
    @PERIOD2STARTDATE datetime,
    @PERIOD2ENDDATE datetime,
    @SITEID uniqueidentifier
)
returns @RESULTS table(
    SEQUENCE integer,
    CATEGORY nvarchar(50),
    PERIOD1NUMDONORS integer default 0,
    PERIOD1AMOUNT money default 0.00,
    PERIOD2NUMDONORS integer default 0,
    PERIOD2AMOUNT money default 0.00,
    GAINSLOSSES money default 0.00,
    GAINSLOSSESPERCENT decimal(7,2) default 0.00
)
with execute as caller
as begin

    -- @TYPE 

    --        0 - All constituents

    --        1 - Individuals only

    --        2 - Organizations only


    declare @REVENUE table(
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        AMOUNT money,
        DATE datetime
    )
    insert into @REVENUE
        select
            REVENUE.ID,
            REVENUE.CONSTITUENTID,
            sum(REVENUESPLIT.AMOUNT) [AMOUNT],
            REVENUE.DATE            
        from dbo.REVENUE
        inner join dbo.REVENUESPLIT 
            on REVENUE.ID = REVENUESPLIT.REVENUEID
        inner join dbo.CONSTITUENT
            on REVENUE.CONSTITUENTID = CONSTITUENT.ID
        where REVENUE.TRANSACTIONTYPECODE = 0            -- all donations, regardless of application type

            and REVENUE.DATE <= @PERIOD2ENDDATE
            and 
            (
                @TYPE = 0
                or
                (@TYPE = 1 and CONSTITUENT.ISORGANIZATION = 0)
                or
                (@TYPE = 2 and CONSTITUENT.ISORGANIZATION = 1)
            )
            and (@SITEID is null or @SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(REVENUESPLIT.DESIGNATIONID))

        group by REVENUE.ID, REVENUE.CONSTITUENTID, REVENUE.TRANSACTIONTYPECODE, REVENUE.DATE;

    declare @REVENUE_PRIOR table(
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        AMOUNT money,
        DATE datetime
    )
    insert into @REVENUE_PRIOR
        select *
        from @REVENUE
        where DATE < @PERIOD1STARTDATE;

    declare @REVENUE_PERIOD1 table(
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        AMOUNT money,
        DATE datetime
    )
    insert into @REVENUE_PERIOD1
        select *
        from @REVENUE
        where DATE between @PERIOD1STARTDATE and @PERIOD1ENDDATE

    declare @REVENUE_PERIOD2 table(
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        AMOUNT money,
        DATE datetime
    )
    insert into @REVENUE_PERIOD2
        select *
        from @REVENUE
        where DATE between @PERIOD2STARTDATE and @PERIOD2ENDDATE

    -- New

    insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD2NUMDONORS, PERIOD2AMOUNT)
        select
            1,
            'New',
            count(Distinct CONSTITUENTID),
            coalesce(sum(AMOUNT), 0)
        from @REVENUE_PERIOD2 period2
        where not exists (select CONSTITUENTID from @REVENUE_PERIOD1 period1 where period1.CONSTITUENTID = period2.CONSTITUENTID)
            and not exists (select CONSTITUENTID from @REVENUE_PRIOR prior where prior.CONSTITUENTID = period2.CONSTITUENTID);

        -- update New row with new donors for period 1        

        declare @PERIOD1NUMDONORS_New integer;
        declare @PERIOD1AMOUNT_New money;

        select
            @PERIOD1NUMDONORS_New = count(distinct CONSTITUENTID),
            @PERIOD1AMOUNT_New = coalesce(sum(AMOUNT), 0)
        from @REVENUE_PERIOD1 period1
        where not exists (select CONSTITUENTID from @REVENUE_PRIOR prior where prior.CONSTITUENTID = period1.CONSTITUENTID); 

        update @RESULTS set
            PERIOD1NUMDONORS = @PERIOD1NUMDONORS_New,
            PERIOD1AMOUNT = @PERIOD1AMOUNT_New;


    -- Recapture

    insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD2NUMDONORS, PERIOD2AMOUNT)
        select
            2,
            'Recaptured',
            count(Distinct CONSTITUENTID),
            coalesce(sum(AMOUNT), 0)
        from @REVENUE_PERIOD2 period2
        where not exists (select CONSTITUENTID from @REVENUE_PERIOD1 period1 where period1.CONSTITUENTID = period2.CONSTITUENTID)
            and exists (select CONSTITUENTID from @REVENUE_PRIOR prior where prior.CONSTITUENTID = period2.CONSTITUENTID);

    -- Create temp tables for calculating Same, Upgrades and Downgrades

    declare @TEMP_PERIOD1 table(
        CONSTITUENTID uniqueidentifier,
        AMOUNT money
    )

    insert into @TEMP_PERIOD1 (CONSTITUENTID, AMOUNT)
        select
            CONSTITUENTID,
            coalesce(sum(AMOUNT), 0)
        from @REVENUE_PERIOD1 period1
        where exists (select CONSTITUENTID from @REVENUE_PERIOD2 period2 where period2.CONSTITUENTID = period1.CONSTITUENTID)
        group by CONSTITUENTID;

    declare @TEMP_PERIOD2 table(
        CONSTITUENTID uniqueidentifier,
        AMOUNT money
    )    

    insert into @TEMP_PERIOD2 (CONSTITUENTID, AMOUNT)
        select
            CONSTITUENTID,
            coalesce(sum(AMOUNT), 0)
        from @REVENUE_PERIOD2 period2
        where exists (select CONSTITUENTID from @REVENUE_PERIOD1 period1 where period1.CONSTITUENTID = period2.CONSTITUENTID)
        group by CONSTITUENTID;

    -- Upgrade

        insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD1NUMDONORS, PERIOD1AMOUNT, PERIOD2NUMDONORS, PERIOD2AMOUNT)
            select
                3,
                'Upgraded',
                count(Distinct period1.CONSTITUENTID),
                coalesce(sum(period1.AMOUNT), 0),
                count(Distinct period2.CONSTITUENTID),
                coalesce(sum(period2.AMOUNT), 0)
            from @TEMP_PERIOD1 period1
            inner join @TEMP_PERIOD2 period2
                on period1.CONSTITUENTID = period2.CONSTITUENTID
            where period1.AMOUNT < period2.AMOUNT;

    -- Same

    insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD1NUMDONORS, PERIOD1AMOUNT, PERIOD2NUMDONORS, PERIOD2AMOUNT)
        select
            4,
            'Same',
            count(Distinct period1.CONSTITUENTID),
            coalesce(sum(period1.AMOUNT), 0),
            count(Distinct period2.CONSTITUENTID),
            coalesce(sum(period2.AMOUNT), 0)
        from @TEMP_PERIOD1 period1
        inner join @TEMP_PERIOD2 period2
            on period1.CONSTITUENTID = period2.CONSTITUENTID
        where period1.AMOUNT = period2.AMOUNT;

    -- Downgrade

    insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD1NUMDONORS, PERIOD1AMOUNT, PERIOD2NUMDONORS, PERIOD2AMOUNT)
        select
            5,
            'Downgraded',
            count(Distinct period1.CONSTITUENTID),
            coalesce(sum(period1.AMOUNT), 0),
            count(Distinct period2.CONSTITUENTID),
            coalesce(sum(period2.AMOUNT), 0)
        from @TEMP_PERIOD1 period1
        inner join @TEMP_PERIOD2 period2
            on period1.CONSTITUENTID = period2.CONSTITUENTID
        where period1.AMOUNT > period2.AMOUNT;

    -- Lapsed new

    insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD1NUMDONORS, PERIOD1AMOUNT)
        select
            6,
            'Lapsed new',
            count(Distinct CONSTITUENTID),
            coalesce(sum(AMOUNT), 0)
        from @REVENUE_PERIOD1 period1
        where not exists (select CONSTITUENTID from @REVENUE_PRIOR prior where prior.CONSTITUENTID = period1.CONSTITUENTID)
            and not exists (select CONSTITUENTID from @REVENUE_PERIOD2 period2 where period2.CONSTITUENTID = period1.CONSTITUENTID)

    -- Lapsed repeat

    insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD1NUMDONORS, PERIOD1AMOUNT)
        select
            7,
            'Lapsed repeat',
            count(Distinct CONSTITUENTID),
            coalesce(sum(AMOUNT), 0)
        from @REVENUE_PERIOD1 period1
        where exists (select CONSTITUENTID from @REVENUE_PRIOR prior where prior.CONSTITUENTID = period1.CONSTITUENTID)
            and not exists (select CONSTITUENTID from @REVENUE_PERIOD2 period2 where period2.CONSTITUENTID = period1.CONSTITUENTID)

    -- Delete rows with no values

    delete @RESULTS where PERIOD1NUMDONORS = 0 and PERIOD2NUMDONORS = 0;

    -- Total

    if exists (select CATEGORY from @RESULTS)
        begin
            insert into @RESULTS (SEQUENCE, CATEGORY, PERIOD1NUMDONORS, PERIOD1AMOUNT, PERIOD2NUMDONORS, PERIOD2AMOUNT, GAINSLOSSES)
                select
                    8,
                    'Total',
                    sum(PERIOD1NUMDONORS),
                    sum(PERIOD1AMOUNT),
                    sum(PERIOD2NUMDONORS),
                    sum(PERIOD2AMOUNT),
                    sum(GAINSLOSSES)
                from @RESULTS

            -- Update results table to include Gains/Losses

            update @RESULTS set
                GAINSLOSSES = PERIOD2AMOUNT - PERIOD1AMOUNT

            update @RESULTS set
                GAINSLOSSESPERCENT = 
                        case
                            when PERIOD1AMOUNT = 0 then 1.00
                            else GAINSLOSSES / PERIOD1AMOUNT
                        end
                where GAINSLOSSES <> 0
        end

    return;
end