USP_KPI_REVENUEGROWTH

CREATE procedure dbo.USP_KPI_REVENUEGROWTH
(
    @VALUE decimal(19, 2) output, 
    @ASOFDATE datetime,
    @CAMPAIGNID uniqueidentifier,
    @DESIGNATIONID uniqueidentifier,
    @YEARTYPE tinyint = 0,
    @COMPARISONPERIODTYPECODE tinyint = 1
)
as
    set nocount on;

    declare @PREVIOUSFROMDATE datetime;
    declare @PREVIOUSTODATE datetime;
    declare @THISFROMDATE datetime;
    declare @THISTODATE datetime = @ASOFDATE;

    -- this year to date versus prior year total

    if @COMPARISONPERIODTYPECODE = 0
    begin
        if @YEARTYPE = 0
        begin
            exec dbo.USP_RESOLVEDATEFILTER 16, @THISFROMDATE output, null, @ASOFDATE;
            exec dbo.USP_RESOLVEDATEFILTER 17, @PREVIOUSFROMDATE output, @PREVIOUSTODATE output, @ASOFDATE;
        end else 
        begin
            exec dbo.USP_RESOLVEDATEFILTER 11, @THISFROMDATE output, null, @ASOFDATE;
            exec dbo.USP_RESOLVEDATEFILTER 12, @PREVIOUSFROMDATE output, @PREVIOUSTODATE output, @ASOFDATE;
        end
    end

    -- this year to date versus prior year to same date

    if @COMPARISONPERIODTYPECODE = 1
    begin
        if @YEARTYPE = 0
            exec dbo.USP_RESOLVEDATEFILTER 16, @THISFROMDATE output, null, @ASOFDATE;
        else
            exec dbo.USP_RESOLVEDATEFILTER 11, @THISFROMDATE output, null, @ASOFDATE;

        set @PREVIOUSFROMDATE = dateadd(year, -1, @THISFROMDATE);
        set @PREVIOUSTODATE = dateadd(year, -1, @ASOFDATE);
    end

    -- last 12 months to 12 months prior to that

    if @COMPARISONPERIODTYPECODE = 2
    begin
        exec dbo.USP_RESOLVEDATEFILTER 9, @THISFROMDATE output, @THISTODATE output, @ASOFDATE;
        set @PREVIOUSFROMDATE = dateadd(year,-1,@THISFROMDATE);
        set @PREVIOUSTODATE = dateadd(year,-1,@THISTODATE);
    end

    declare @CAMPAIGNHIERARCHYPATH hierarchyid;
    select 
        @CAMPAIGNHIERARCHYPATH=HIERARCHYPATH
    from dbo.CAMPAIGN
        where CAMPAIGN.ID = @CAMPAIGNID;

    declare @PREVIOUSPERIODSUM money;
    declare @THISPERIODSUM money;

    with REVENUE_CTE as
    (
        select
            REVENUE.ID,
            REVENUE.CONSTITUENTID,
            sum(REVENUESPLIT.ORGANIZATIONAMOUNT) [AMOUNT],
            REVENUE.DATE
        from
            dbo.REVENUE
        inner join
            dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
        where
            (
                (REVENUE.TRANSACTIONTYPECODE = 1) --Pledge

                or 
                (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0, 3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))) --Payment (Gift and Recurring Gift)

            )    
        and
            (
                (@DESIGNATIONID is null)
                or 
                exists (
                    select top(1)
                        REVENUESPLIT.ID 
                    from 
                        dbo.REVENUESPLIT 
                    where 
                        REVENUESPLIT.REVENUEID = REVENUE.ID 
                    and 
                        REVENUESPLIT.DESIGNATIONID = @DESIGNATIONID
                )            
            )
        and
            (
                (@CAMPAIGNID is null)
                or 
                exists (
                    select top(1)
                        REVENUESPLITCAMPAIGN.ID
                    from 
                        dbo.REVENUESPLITCAMPAIGN 
                    inner join 
                        CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID 
                    inner join 
                        REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
                    where 
                        REVENUESPLIT.REVENUEID = REVENUE.ID 
                    and 
                        CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                )
            )
        group by
            REVENUE.ID, REVENUE.CONSTITUENTID, REVENUE.TRANSACTIONTYPECODE, REVENUE.DATE
    )
    select
        @THISPERIODSUM = sum(
                case 
                    when REVENUE_CTE.DATE between @THISFROMDATE and @THISTODATE then REVENUE_CTE.AMOUNT
                    else 0
                end
        ),
        @PREVIOUSPERIODSUM = sum(
                case
                    when REVENUE_CTE.DATE between @PREVIOUSFROMDATE and @PREVIOUSTODATE then REVENUE_CTE.AMOUNT
                    else 0
                end
        )
    from
        REVENUE_CTE

    set @PREVIOUSPERIODSUM = coalesce(@PREVIOUSPERIODSUM, 0);
    set @THISPERIODSUM = coalesce(@THISPERIODSUM, 0);

    -- handle divide by zero

    if @PREVIOUSPERIODSUM = 0
        if @THISPERIODSUM = 0
            set @VALUE = 0;
        else
            set @VALUE = 1;
    else
        set @VALUE = (@THISPERIODSUM - @PREVIOUSPERIODSUM) / @PREVIOUSPERIODSUM;