USP_REPORT_FEPBENCHMARKINGCOMPARISON_GETORGSDATA

create procedure dbo.USP_REPORT_FEPBENCHMARKINGCOMPARISON_GETORGSDATA
(
    @YEAR dbo.UDT_YEAR,
    @SITEID uniqueidentifier = null
)
with execute as owner
as    
    declare @TYPE tinyint = 0;        -- Include both organizations and individuals

    declare @PERIOD1STARTDATE datetime = '01/01/' + cast(@YEAR - 1 as nvarchar(10));
    declare @PERIOD1ENDDATE datetime = '12/31/' + cast(@YEAR - 1 as nvarchar(10));
    declare @PERIOD2STARTDATE datetime = '01/01/' + cast(@YEAR as nvarchar(10));
    declare @PERIOD2ENDDATE datetime = '12/31/' + cast(@YEAR as nvarchar(10));

    declare @TEMP table (SEQUENCE integer, CATEGORY nvarchar(50), NUMDONORS_PERIOD1 integer, AMOUNT_PERIOD1 money, NUMDONORS_PERIOD2 integer, AMOUNT_PERIOD2 money)

    insert into @TEMP (SEQUENCE, CATEGORY, NUMDONORS_PERIOD1, AMOUNT_PERIOD1, NUMDONORS_PERIOD2, AMOUNT_PERIOD2)
        select
            SEQUENCE,
            CATEGORY,
            PERIOD1NUMDONORS,
            PERIOD1AMOUNT,
            PERIOD2NUMDONORS,
            PERIOD2AMOUNT
        from dbo.UFN_FEPSUBMITPROCESS_GETDATA(@TYPE, @PERIOD1STARTDATE,    @PERIOD1ENDDATE, @PERIOD2STARTDATE,    @PERIOD2ENDDATE, @SITEID);

    declare @NUMDONORS_NEW integer = 0;
    declare @AMOUNT_NEW money = 0;
    declare @NUMDONORS_RECAPTURED integer = 0;
    declare @AMOUNT_RECAPTURED money = 0;
    declare @NUMDONORS_UPGRADED integer = 0;
    declare @AMOUNT_UPGRADED money = 0;
    declare @NUMDONORS_SAME integer = 0;
    declare @AMOUNT_SAME money = 0;
    declare @NUMDONORS_DOWNGRADED integer = 0;
    declare @AMOUNT_DOWNGRADED money = 0;
    declare @NUMDONORS_LAPSEDNEW integer = 0;
    declare @AMOUNT_LAPSEDNEW money = 0;
    declare @NUMDONORS_LAPSEDOTHER integer = 0;
    declare @AMOUNT_LAPSEDOTHER money = 0;

    select
        @NUMDONORS_NEW = NUMDONORS_PERIOD2,
        @AMOUNT_NEW = AMOUNT_PERIOD2
    from @TEMP
    where CATEGORY = 'New';

    select
        @NUMDONORS_RECAPTURED = NUMDONORS_PERIOD2,
        @AMOUNT_RECAPTURED = AMOUNT_PERIOD2
    from @TEMP
    where CATEGORY = 'Recapture';

    select
        @NUMDONORS_UPGRADED = NUMDONORS_PERIOD2,
        @AMOUNT_UPGRADED = AMOUNT_PERIOD2 - AMOUNT_PERIOD1
    from @TEMP
    where CATEGORY = 'Upgrade';

    select
        @NUMDONORS_SAME = NUMDONORS_PERIOD2,
        @AMOUNT_SAME = AMOUNT_PERIOD2
    from @TEMP
    where CATEGORY = 'Same';

    select
        @NUMDONORS_DOWNGRADED = NUMDONORS_PERIOD2,
        @AMOUNT_DOWNGRADED = AMOUNT_PERIOD1 - AMOUNT_PERIOD2
    from @TEMP
    where CATEGORY = 'Downgrade';

    select
        @NUMDONORS_LAPSEDNEW = NUMDONORS_PERIOD1,
        @AMOUNT_LAPSEDNEW = AMOUNT_PERIOD1
    from @TEMP
    where CATEGORY = 'Lapsed new';

    select
        @NUMDONORS_LAPSEDOTHER = NUMDONORS_PERIOD1,
        @AMOUNT_LAPSEDOTHER = AMOUNT_PERIOD1
    from @TEMP
    where CATEGORY = 'Lapsed repeat';

    select
        @NUMDONORS_NEW as NUMDONORS_NEW,
        @AMOUNT_NEW as AMOUNT_NEW,
        @NUMDONORS_RECAPTURED as NUMDONORS_RECAPTURED,
        @AMOUNT_RECAPTURED as AMOUNT_RECAPTURED,
        @NUMDONORS_UPGRADED as NUMDONORS_UPGRADED,
        @AMOUNT_UPGRADED as AMOUNT_UPGRADED,
        @NUMDONORS_SAME as NUMDONORS_SAME,
        @AMOUNT_SAME as AMOUNT_SAME,
        @NUMDONORS_DOWNGRADED as NUMDONORS_DOWNGRADED,
        @AMOUNT_DOWNGRADED as AMOUNT_DOWNGRADED,
        @NUMDONORS_LAPSEDNEW as NUMDONORS_LAPSEDNEW,
        @AMOUNT_LAPSEDNEW as AMOUNT_LAPSEDNEW,
        @NUMDONORS_LAPSEDOTHER as NUMDONORS_LAPSEDOTHER,
        @AMOUNT_LAPSEDOTHER as AMOUNT_LAPSEDOTHER;